Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««56789»»»

Fun(?) with DATETIME2 Expand / Collapse
Author
Message
Posted Monday, February 14, 2011 7:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:37 AM
Points: 5,575, Visits: 6,359
Jamie Longstreet-481950 (1/25/2011)
What happens when:

Declare @dt datetime2
SET @dt='1752-09-09'
DECLARE @dt2 datetime
DECLARE @dt3 varbinary
SET @dt3=CONVERT(VARBINARY,@dt)
SELECT @dt3
SELECT cast(CAST(@dt3 AS NVARCHAR) AS varbinary)
SELECT CONVERT(DATETIME2,cast(CAST(@dt3 AS NVARCHAR) AS varbinary))
SET @dt2=@dt
SELECT @dt

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

Conversion failed when converting date and/or time from character string.

It is important that when you upgrade from Ver 90 SQL to Ver 100 that you explicitly declare previous datetime data to prevent the accidental assignment of a longer type to a shorter type.

Also, note above... how does one convert to a numeric value?


Jaime, I'm not sure I understand the above in reference to the below:

You're getting errors converting from Varbinary and Nvarchar to a datetime2, but what has that got to do with your original post about GetDate() defaulting to Datetime2 and having problems converting to datetime?

Jamie Longstreet-481950 (1/25/2011)

Something else is a bit disturbing here... the datetime2 is often a default time now for getdate() (or seems to be) and it can be problematic in databases created prior to the new variable type as the prefix ".000000" on the end cannot be read and generates an error. I have had to write workarounds more than once in the database.


I've tried and tried to understand the connection between the two and I'm missing something. The following code doesn't cause me any conversion issues. SELECT GetDate(), Convert(datetime2,GetDate()), Convert(datetime,GetDate()).

What am I missing that you're having problems with?


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1063491
Posted Tuesday, February 15, 2011 9:43 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 18, 2014 11:32 AM
Points: 2,669, Visits: 779
Here is a better example:

http://connect.microsoft.com/SQLServer/feedback/details/573675/error-converting-data-type-varchar-to-datetime


Jamie
Post #1064383
Posted Tuesday, February 15, 2011 10:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:37 AM
Points: 5,575, Visits: 6,359
Oh, okay. I think I get what the issue is. Thank you for the second link.

I just don't understand why people would use varchar (or any string) to represent dates given the problems that always causes.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1064404
Posted Tuesday, February 15, 2011 12:06 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 18, 2014 11:32 AM
Points: 2,669, Visits: 779
I prefer something I can rely on to work for all cases with dates... my favorite is

CONVERT(varchar(8),getdate(),112)='20110215'

because it is easy to remember and there are not special characters and it is always numeric. Not so good if I need a time, but for many processes, (auditing aside) there is no need to check time.


Jamie
Post #1064489
Posted Tuesday, February 15, 2011 1:38 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, Visits: 3,039
Jamie Longstreet-481950 (2/15/2011)
I prefer something I can rely on to work for all cases with dates... my favorite is

CONVERT(varchar(8),getdate(),112)='20110215'

because it is easy to remember and there are not special characters and it is always numeric. Not so good if I need a time, but for many processes, (auditing aside) there is no need to check time.

Looks familiar... I use format 112 a lot, too, but generally with char(8) rather than varchar(8). Is there an advantage to use varchar() that I'm missing? Or is it just one of those "depends" things?
Post #1064541
Posted Tuesday, February 15, 2011 4:29 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 18, 2014 11:32 AM
Points: 2,669, Visits: 779
No, char(8) is better. Like I said, sometimes I just prefer not to have to think about it.

Jamie
Post #1064628
Posted Wednesday, February 16, 2011 4:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:37 AM
Points: 5,575, Visits: 6,359
john.arnott (2/15/2011)
Jamie Longstreet-481950 (2/15/2011)
I prefer something I can rely on to work for all cases with dates... my favorite is

CONVERT(varchar(8),getdate(),112)='20110215'

because it is easy to remember and there are not special characters and it is always numeric. Not so good if I need a time, but for many processes, (auditing aside) there is no need to check time.

Looks familiar... I use format 112 a lot, too, but generally with char(8) rather than varchar(8). Is there an advantage to use varchar() that I'm missing? Or is it just one of those "depends" things?


varchar(8) is not only unnecessary, but it requires extra overhead (all var data types do) to maintain it's flexibility between 1-8 "spaces". (Space is a poor word choice on my part, but I'm too tired to remember the proper word here).

Stick with char(8) since your "dates" will never be less or more. In the long run, it could save you some performance and space problems.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1064827
Posted Wednesday, February 16, 2011 4:27 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 18, 2014 11:32 AM
Points: 2,669, Visits: 779
char - characters...

The brain sleeps when it runs out of energy. Often it is smarter than we are.


Jamie
Post #1064830
Posted Wednesday, February 16, 2011 4:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:37 AM
Points: 5,575, Visits: 6,359
Jamie Longstreet-481950 (2/16/2011)
The brain sleeps when it runs out of energy. Often it is smarter than we are.


Yeah, and supposedly I just woke up from a good night's sleep. @=)

Actually, characters is not the word I was looking for. There is another, more appropriate SQL Server word that I just can't remember. It's not digits either.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1064840
Posted Wednesday, February 16, 2011 4:39 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 18, 2014 11:32 AM
Points: 2,669, Visits: 779
ansi?

Jamie
Post #1064842
« Prev Topic | Next Topic »

Add to briefcase «««56789»»»

Permissions Expand / Collapse