April 18, 2012 at 3:09 am
Hi, I'm trying to create some DDL to use in a post on this form. Stumped on how to insert datetime value into newly created datetime column.
The table:
create table Case_Review
(Case_ID int,
Revision int,
Review_Status varchar(30),
Change_Date datetime);
A sample of the insert statement:
insert into Case_Review
values
(220, 1, 'Proposed', 2012-04-17),
(220, 2, 'Proposed', 2012-04-17),
(220, 3, 'Proposed', 2012-04-18);
Am trying to simulate existence of multiple rows sharing same year-month-day, but differnt time. But, if I try to add time to end of, the insert fails.
insert into Case_Review
values
(220, 1, 'Proposed', 2012-04-17 12:01),
(220, 2, 'Proposed', 2012-04-17 12:02),
(220, 3, 'Proposed', 2012-04-18 14:37);
get this error
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '12'.
How to insert value into datetime field so that it includes time?
--Quote me
April 18, 2012 at 3:55 am
Try putting your date strings in quotes, like:
'2012-01-01' or '2012-01-01 23:59'
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 18, 2012 at 3:55 am
insert into Case_Review
values
(220, 1, 'Proposed', '2012-04-17 12:01'),
(220, 2, 'Proposed', '2012-04-17 12:02'),
(220, 3, 'Proposed', '2012-04-18 14:37');
April 18, 2012 at 4:17 am
Thanks both.
I did that and the insert part executed, but the results in the table were unexpected.
Insert
insert into Case_Review
values
(220, 1, 'Proposed', '2012-04-17 12:17'),
(220, 2, 'Proposed', '2012-04-17 14:05'),
(220, 3, 'Proposed', '2012-04-18 17:03'),
(220, 4, 'Proposed', '2012-04-18 17:05'),
(220, 5, 'Active', '2012-04-19 12:20'),
(220, 6, 'Active', '2012-04-19 12:45'),
(220, 7, 'Active', '2012-04-20 08:05'),
(220, 8, 'Resolved', '2012-04-27 12:21'),
(220, 9, 'Resolved', '2012-04-27 12:45'),
(220, 10, 'Resolved', '2012-04-28 12:00'),
(220, 11, 'Resolved', '2012-04-28 12:01'),
(230, 1, 'Proposed', '2012-04-17 01:00'),
(230, 2, 'Proposed', '2012-05-29 07:00'),
(230, 3, 'Active', '2012-05-29 12:00'),
(230, 4, 'Active', '2012-05-29 12:05'),
(230, 5, 'Active', '2012-06-30 12:00'),
(230, 6, 'Closed', '2012-07-01 13:00'),
(230, 7, 'Closed', '2012-07-01 13:01'),
(260, 1, 'Proposed', '2012-04-17 14:00'),
(260, 2, 'Active', '2012-07-10 14:25'),
(260, 3, 'Active', '2012-07-10 15:00'),
(260, 4, 'Closed', '2012-10-20 15:20');
The result in the table *** note the dates are for year 1905
Case_IDRevisionReview_StatusChange_Date
2201Proposed1905-06-15 00:00:00.000
2202Proposed1905-06-15 00:00:00.000
2203Proposed1905-06-14 00:00:00.000
2204Proposed1905-06-14 00:00:00.000
2205Active1905-06-13 00:00:00.000
2206Active1905-06-13 00:00:00.000
2207Active1905-06-12 00:00:00.000
2208Resolved1905-06-05 00:00:00.000
2209Resolved1905-06-05 00:00:00.000
22010Resolved1905-06-04 00:00:00.000
--Quote me
April 18, 2012 at 4:40 am
Mighty weird. I am baffled.
Perhaps some internal setting having to do with the SQL base date or your client's date/timezone setting?
This will take an expert to offer an explanation.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 18, 2012 at 7:51 am
im not sure whats going on but i thank you for taking the time to read how to post to the forumn and working up the DDL and Sample data. im sure the experts will be able to at least point you in the right direction.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 18, 2012 at 8:30 am
if it's just DDL you're trying to create, try on second laptop (personal one?) and see if still happening. maybe you can still make progress on creating your DDL.:-)
April 18, 2012 at 8:41 am
polkadot (4/18/2012)
Thanks both.I did that and the insert part executed, but the results in the table were unexpected.
Insert
insert into Case_Review
values
(220, 1, 'Proposed', '2012-04-17 12:17'),
(220, 2, 'Proposed', '2012-04-17 14:05'),
(220, 3, 'Proposed', '2012-04-18 17:03'),
(220, 4, 'Proposed', '2012-04-18 17:05'),
(220, 5, 'Active', '2012-04-19 12:20'),
(220, 6, 'Active', '2012-04-19 12:45'),
(220, 7, 'Active', '2012-04-20 08:05'),
(220, 8, 'Resolved', '2012-04-27 12:21'),
(220, 9, 'Resolved', '2012-04-27 12:45'),
(220, 10, 'Resolved', '2012-04-28 12:00'),
(220, 11, 'Resolved', '2012-04-28 12:01'),
(230, 1, 'Proposed', '2012-04-17 01:00'),
(230, 2, 'Proposed', '2012-05-29 07:00'),
(230, 3, 'Active', '2012-05-29 12:00'),
(230, 4, 'Active', '2012-05-29 12:05'),
(230, 5, 'Active', '2012-06-30 12:00'),
(230, 6, 'Closed', '2012-07-01 13:00'),
(230, 7, 'Closed', '2012-07-01 13:01'),
(260, 1, 'Proposed', '2012-04-17 14:00'),
(260, 2, 'Active', '2012-07-10 14:25'),
(260, 3, 'Active', '2012-07-10 15:00'),
(260, 4, 'Closed', '2012-10-20 15:20');
The result in the table *** note the dates are for year 1905
Case_IDRevisionReview_StatusChange_Date
2201Proposed1905-06-15 00:00:00.000
2202Proposed1905-06-15 00:00:00.000
2203Proposed1905-06-14 00:00:00.000
2204Proposed1905-06-14 00:00:00.000
2205Active1905-06-13 00:00:00.000
2206Active1905-06-13 00:00:00.000
2207Active1905-06-12 00:00:00.000
2208Resolved1905-06-05 00:00:00.000
2209Resolved1905-06-05 00:00:00.000
22010Resolved1905-06-04 00:00:00.000
Take the hypens out of the dates. Use '20120501' instead of '2012-05-01' for instance.
April 18, 2012 at 8:46 am
You might also want to post the results from the following:
SELECT CAST(0 AS DATETIME)
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 18, 2012 at 9:58 am
ok dwain.c
results of SELECT CAST(0 AS DATETIME) are:
1900-01-01 00:00:00.000
Anyway, I deleted the table and reran the DDL. **then refreshed the database** and then ran select. This time the date times are fine. Moving ahead. Thanks.
--Quote me
April 18, 2012 at 10:13 am
and then I scrapped my table and re - ran DDL because I had a new column to add, and same thing. When I refreshed database, however, and ran select statement after that, the datetime 'corrected itself'.
--Quote me
April 18, 2012 at 11:45 am
Does that mean you are good to go, or that you still need help?
April 18, 2012 at 2:09 pm
If you get into trouble with this sort of thing it's probably best to use the ISO datetime string format: yyyy-mm-ddThh:mi:ss.mmm - note that there is no space anywhere in the ISO datetime string - along with CONVERT, for example CONVERT(datetime2,'2012-04-18T20:56:32.000',126). Substitute datetime for datetime2 if you are stuck with the older type) This should work regardless of the server settings for specifying values for any datetime or datetime2 field or variable.
If you want to use the ODBC canonical format you still have to use CONVERT to override any server options that would otherwise prevent it from working, but the style parameter now has to be 121 instead of 126. In effect CONVERT(datetime2,'2012-04-18T20:56:32.000',126) means exactly the same as CONVERT(datetime2,'2012-04-18 20:56:32.000',121) so where one works so should the other.
Tom
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply