Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Convert 'Nov 7' to datetime
Message
From
10/11/2018 08:25:13
Cetin Basoz
Engineerica Inc.
Izmir, Turkey
 
 
To
09/11/2018 15:48:22
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Environment versions
SQL Server:
SQL Server 2016
OS:
Windows 7
Miscellaneous
Thread ID:
01663259
Message ID:
01663265
Views:
109
You just need to poke the year in there before making conversion to a datetime:
declare @fdate varchar(20), @newdate datetime;
set @fdate = 'Nov 07 04:30';
set @newdate = stuff(@fdate, 8, 0, cast(year(getdate()) as char(5)));  -- 'Nov 07 2018 04:30'
select @newdate;
Here is a link to DbFiddle demo

PS: I assume server language is English.

>Hey guys, I'm back
>
>I'm trying to get something that looks like 'Nov 7 04:30' to convert to the equivalent SQL datetime value.
>Everything I'm trying is bringing back 'Nov 7 2018 4:30 AM. I can get it as far as a string 2018-11-07 04:30, but it will not convert to a datetime
>
>declare @fdate varchar(20), @f2 int, @f3 int, @f4 int, @t varchar(5), @nd varchar(20), @newdate datetime
>set @fdate = 'Nov 07 04:30'
>set @t = right(@fdate, 5)
>print isdate(@fdate)
>set @f2 = datepart(MM,@fdate) --str(@fdate,1,3))
>set @f3 = convert(int, substring(@fdate, 5,2))
>set @f4 = iif((@f2 = 12) and (datepart(MM, getdate()) = 1), year(getdate()) - 1, year(getdate()))
>set @nd = str(@f4,4) + '-' + replace(str(@f2,2), space(1), '0') + '-' + replace(str(@f3,2), space(1), '0') + ' ' + @t
>--set @nd = replace(str(@f2,2), space(1), '0') + replace(str(@f3,2), space(1), '0') + str(@f4,4)-- + @t
>--set @newdate = convert(datetime, @nd)
>set @newdate = cast(@nd as datetime)
>print @f2
>print @f3
>print @f4
>print @nd
>print @newdate
Çetin Basöz

The way to Go
Flutter - For mobile, web and desktop.
World's most advanced open source relational database.
.Net for foxheads - Blog (main)
FoxSharp - Blog (mirror)
Welcome to FoxyClasses

LinqPad - C#,VB,F#,SQL,eSQL ... scratchpad
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform