question

nidheesh.r.pillai avatar image
nidheesh.r.pillai asked

Tracking a scenario - Compute and store a date and time value as 'YYYY-MM-DD HH:MM'

In SQL Server 2008 is it possible to compute and store a date and time value as **'YYYY-MM-DD HH:MM'**? If YES, how is it achieveable?
sql-server-2008datetime-formats
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

please don't close the questions - simply marking them as answered is enough
1 Like 1 ·
Kev Riley avatar image
Kev Riley answered
In SQL Server you don't store date/time in a particular format, you simply store it in the relevant datatype: - date - datetime - datetime2 - time - smalldatetime - datetimeoffset http://msdn.microsoft.com/en-us/library/ms186724(v=sql.100).aspx Then when you want to display it in a particular format, use the application layer to apply formating, or if you must, use styles in the `CONVERT` function : http://msdn.microsoft.com/en-us/library/ms187928(v=sql.100).aspx If however you mean you want to truncate values down to a particular level, e.g. 2 Dec 2014 10:55:34 -> 2 Dec 2014 10:55:00 then you can acheive this by declare @dt datetime set @dt = '2 Dec 2014 10:55:34' select dateadd(minute,datediff(minute, 0, @dt),0) You can then display this however you want select convert(varchar, dateadd(minute,datediff(minute, 0, @dt),0), 120) ------------------------------ 2014-12-02 10:55:00 (1 row(s) affected)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thankyou @KevRiley Yes, I was looking for eliminating the seconds and the milli-seconds part from the date. My requirement is that I want to compare a static date (like '2014-12-02 05:00:00.000') which is stored as date time in my table with the current system datetime (Getdate()), since the seconds and the milli-seconds part may never make the match to be exact, I was looking for something like '2014-12-02 05:00' = GETDATE(), assuming GETDATE is now between '2014-12-02 05:00:00' and '2014-12-02 05:00:59'. I think your example abve can help me achieve that.
0 Likes 0 ·
vivekgrover44 avatar image
vivekgrover44 answered
Please try this ![alt text][1] [1]: /storage/temp/1820-date+time+format.jpg

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.