Help with date formatting - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help with date formatting

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-10-05 : 09:51:03

I am trying to get today's date in the following format:

two digit year, month, day, hour, and minutes

Example: 1110050936

Can someone help me out?

Thanks!

lappin
Posting Yak Master

182 Posts

Posted - 2011-10-05 : 10:05:53

May be a better way, but this works:

declare @DT datetime
set @DT = GETDATE()
declare @LeftDT varchar(6)

declare @RightDT varchar(4)
set @LeftDT= convert(VARCHAR(6),@DT,12)
set @RightDT= Replace(convert(VARCHAR(6),@DT,108),':','')

select @LeftDT +'' +@RightDT

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-10-05 : 10:06:28

I also figured it out using this:

select CAST(right(year(GETDATE()),2) AS nvarchar(2)) + CAST(month(GETDATE()) AS nvarchar(2)) + CAST(day(GETDATE()) AS nvarchar(2)) + CAST(DATEPART(hour, GETDATE()) AS nvarchar(2)) + CAST(DATEPART(minute, GETDATE()) AS nvarchar(2))

lappin
Posting Yak Master

182 Posts

Posted - 2011-10-05 : 10:15:54

Not sure if you are using GetDate() just as a demo and you are going to apply to a value in a table. But if you are using GetDate() for real - it's better to put it into a variable, because it can change, e.g. if it runs at 16:59:59 you could conceivably get the 16 hour, but the 00 minute as it changes to 17:00:00, giving a time of 16:00:00 - Not very likely - but possible.
If you are running this against a table I think my method might be faster because it is calling less functions - but you should test.

Reporter
Starting Member

48 Posts

Posted - 2011-10-05 : 10:21:26

integer format :)

declare @d datetime
set @d=getdate()

select convert(varchar(12),@d,12)*10000+
datediff(hh,convert(varchar(12),@d,12),@d)*100+
datediff(mi,convert(varchar(12),@d,12),@d)%60

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-10-05 : 10:33:22

Point taken, thanks to the both of you for the suggestions!

Reporter
Starting Member

48 Posts

Posted - 2011-10-05 : 10:38:07

declare @d datetime
set @d=getdate()

select datepart(yy,@d)%1000*100000000+datepart(mm,@d)*1000000+datepart(dd,@d)*10000+datepart(hh,@d)*100+datepart(mi,@d)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources