I work with a lot of dates in SQL: order dates, shipping dates, edit dates, etc. Many times these dates are stored as a smalldatetime type. That works well for evaluating one date against another. However, the small date time looks like this when selected from the table: 2013-01-29 16:57:35.350. This type of format isn't always the best for reports or even for grouping data up once exported to your program of choice.
The good news is there are a lot of ways you can manipulate those dates in order to get them into a more usable format. I always find myself looking in the same place for SQL date help. At SQL Server Helper date formats page you can sift through a multitude of different date formatting options. My favorites are below.
YYMMDD - select convert(varchar(6), getdate(), 12)[Date]
YYYYMMDD - select convert(varchar(8), getdate(), 112)[Date]
You can replace the getdate() function with the column that contains the date and then you'll see your date formatted as show above.
I had a hell of a time converting a date stored with a midnight timestamp and a separate field with time stored as an integer, such as 91530 for 9:15:30 am. Nick mentioned your twitter linked to SQL posts but I didn't realize they were your own posts! Lindsay Collins
ReplyDeleteWell I hope the article was able to help. Do you actually work with SQL?
ReplyDelete