Understanding Date and Time Functions and Operations in SQL Server
page 4 of 8
by Abhishek Kumar Singh
Feedback
Average Rating: 
Views (Total / Last 10 Days): 54507/ 150

Quick examples to understand DATETIME operations
CREATE TABLE ##T(MyDateTime DATETIME)
 
INSERT INTO ##T (MyDateTime) VALUES('2008-09-23 09:12:44.310')

-- Result: 2008-09-23 09:12:44.310 inserted.

INSERT INTO ##T (MyDateTime) VALUES('20080923'

--Must be passed as string. Result 2008-09-23 00:00:00.000 inserted.

INSERT INTO ##T (MyDateTime) VALUES('23/09/2008'

--Error- The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

--The statement has been terminated. In the format ../../.... SQL Server expects first value as the month. Month value 23 is invalid.

INSERT INTO ##T (MyDateTime) VALUES('09/23/2008')

 --Result 2008-09-23 00:00:00.000 inserted.

INSERT INTO ##T (MyDateTime) VALUES('SEPT 23 2008')

--Error- Conversion failed when converting datetime from character string.

-- Reason- We should either specify three characters for month or full name.

INSERT INTO ##T (MyDateTime) VALUES('SEP 23 2008'

--Result- 2008-09-23 00:00:00.000 inserted.

INSERT INTO ##T (MyDateTime) VALUES('SEPTEMBER 23 2008'

--Result- 2008-09-23 00:00:00.000 inserted.

INSERT INTO ##T (MyDateTime) VALUES('SEP 23 08')

 --Result- 2008-09-23 00:00:00.000 inserted.

INSERT INTO ##T (MyDateTime) VALUES('SEP 23 50')

--Result- 1950-09-23 00:00:00.000 inserted.
--Reason- When we specify only the last two digits of the year, from 00 to 49 will be accepted for the years from 2000 to 2049. I suggest we should always specify four digit years to prevent any unexpected errors. So finally I shall drop the temp table to finish the example.

DROP TABLE ##T

View Entire Article

User Comments

Title: datetime in sqlserver   
Name: murali
Date: 2010-11-11 12:52:06 AM
Comment:
How to alter the datetime to date conversion
Title: Great!!   
Name: Shyam
Date: 2009-12-30 12:33:12 AM
Comment:
Thank you for your nice article.
it Gives me a better understanding to know time Manipulation Function!
Title: awesome   
Name: mahesh
Date: 2009-05-29 4:15:11 PM
Comment:
great job it helped me a lot thank you very much!!!!!!
Title: Great!!!   
Name: Gourik Kumar Bora
Date: 2009-03-31 3:21:57 AM
Comment:
Hi,
Thanks a lot. quite a helpful article..
Title: set datefirst inside function   
Name: ll
Date: 2008-11-17 7:24:10 AM
Comment:
how to use set datefirst inside function
Title: Thanks.   
Name: Abhishek (Author)
Date: 2008-11-13 8:05:34 AM
Comment:
Thanks to all of you for appreaciating my article.
Title: Thanks a Lot   
Name: Krishna Prabhu
Date: 2008-11-13 4:43:50 AM
Comment:
Thanks a lot for giving this artical.It helped in completing project
Title: Thnx a Ton   
Name: Neha Mishra
Date: 2008-11-12 2:15:52 AM
Comment:
Thnx a ton for such a detailed and informatic article. It helped in clearing almost all my doubts.
Title: Thank you   
Name: Vishal s.surana
Date: 2008-11-11 7:44:52 AM
Comment:
Thank you for your nice article.It is very helpful to understand differnt behaviour of datetime datatype.






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-11-21 6:42:55 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search