SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fun(?) with DATETIME2


Fun(?) with DATETIME2

Author
Message
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38014 Visits: 9274
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Jamie-2229
Jamie-2229
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3860 Visits: 857
Here is a better example:

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

Jamie
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38014 Visits: 9274
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Jamie-2229
Jamie-2229
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3860 Visits: 857
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
john.arnott
john.arnott
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3612 Visits: 3059
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?
Jamie-2229
Jamie-2229
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3860 Visits: 857
No, char(8) is better. Like I said, sometimes I just prefer not to have to think about it.

Jamie
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38014 Visits: 9274
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Jamie-2229
Jamie-2229
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3860 Visits: 857
char - characters...

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

Jamie
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38014 Visits: 9274
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Jamie-2229
Jamie-2229
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3860 Visits: 857
ansi?

Jamie
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search