insert DateTime issue

Question

Post reply

insert DateTime issue

rothj

Hall of Fame

Points: 3412
More actions
September 26, 2011 at 11:53 am

#257740

I have a table with 3 columns: Id (int, identity), start_dtime (datetime) and end_dtime (datetime)
I tried to insert a row with this:
INSERT INTO census_date
([start_dtime]
,[end_dtime])
VALUES
('2013-01-01 00:00:00', '2013-01-31 00:00:00')
But I get the error:
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.
What format should the datetime be? Do I need to use Cast?
Thank you

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

if that table is as stated with the date columns as datetime then that insert should work,

any chance there is a trigger on the table?

rothj Hall of Fame Points: 3412 More actions · Answer 2

No trigger.

This is a 2008 sql server. I accidentally posted under 2005 forum.

steveb. SSC-Forever Points: 46733 More actions · Answer 3

can you post your table DDL?

that insert will work 2008 as well

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

You most likely have your ydm order incorrect for your current settings.

the following is the culprit.

select cast('2013-01-31 00:00:00' as datetime)

Now try this:

set dateformat mdy

select cast('2013-01-31 00:00:00' as datetime)

You could also leave off the time if you want all 0's

set dateformat mdy

select cast('2013-01-31' as datetime)

Hope that helps.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

rothj Hall of Fame Points: 3412 More actions · Answer 5

Here is what my table looks like:

CREATE TABLE [schema].[census_date](

[id_col] [int] IDENTITY(1,1) NOT NULL,

[start_dtime] [datetime] NOT NULL,

[end_dtime] [datetime] NOT NULL

I'm fairly sure I have the format correct.

When I run your select cast I get the same format: Example: 2013-01-31 00:00:00.000

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

Using your create statement and your insert it works just fine.

CREATE TABLE #census_date(

[id_col] [int] IDENTITY(1,1) NOT NULL,

[start_dtime] [datetime] NOT NULL,

[end_dtime] [datetime] NOT NULL

)

INSERT INTO #census_date

([start_dtime]

,[end_dtime])

VALUES

('2013-01-01 00:00:00', '2013-01-31 00:00:00')

select * from #census_date

drop table #census_date

Does this throw an exception for you?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

rothj Hall of Fame Points: 3412 More actions · Answer 7

rothj

Hall of Fame

Points: 3412

September 26, 2011 at 12:35 pm

#1386736

No, your code works fine for the temp table.

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

It is the same table that you scripted. I just changed the name to a temp so it would cause issues with your existing table. You provided the create table and a sample insert statement that you are having problems with (thanks for these as it make things a zillion times easier on our end). If this still fails on your system it seems the only possibility left is your date format does not match your insert.

Do both of the following work correctly?

select CAST('2013-01-01 00:00:00' as datetime)

select CAST('2013-01-31 00:00:00' as datetime)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

Also, what do each of these return?

select @@LANGUAGE

exec sp_helplanguage @@language

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

rothj Hall of Fame Points: 3412 More actions · Answer 10

OK, we have the format correct.

I think the problem is 2012 is a leap year.

It is failing on February...specifically the 2013-02-29 date.

The language is us_english and dateformat is mdy.

steveb. SSC-Forever Points: 46733 More actions · Answer 11

rothj (9/27/2011)
OK, we have the format correct.
I think the problem is 2012 is a leap year.
It is failing on February...specifically the 2013-02-29 date.
The language is us_english and dateformat is mdy.

Yes that date will cause the problem as it dosen't exist, I didn't see that in the original question so I didn't consider it

rothj Hall of Fame Points: 3412 More actions · Answer 12

Very sorry I did not include that. My bad.

Sorry to waste your time. I initially just thought I had the format incorrect.

How do I get around this? It is a valid date for a leap yaer, correct?

steveb. SSC-Forever Points: 46733 More actions · Answer 13

rothj (9/27/2011)
Very sorry I did not include that. My bad.
Sorry to waste your time. I initially just thought I had the format incorrect.
How do I get around this? It is a valid date for a leap yaer, correct?

no worries, it was not a waste of time

as for the invalid date you need to check where this date is being created and sort out the issue there. Otherwise you need to work out which date that the 29 Feb needs to be set to on a non leap year, such as 28 Feb or 1 March

rothj Hall of Fame Points: 3412 More actions · Answer 14

Oh!

I have 2013.

2012 is the leap year.

2013-02-29 is not a vaild date.

I'm awake now. :blush:

THANKS Guys!