Date format not working

Question

Date format not working

LoosinMaMind

Hall of Fame

Points: 3366
More actions
November 28, 2010 at 11:42 am

#231608

Hi
can anyone tell me why when i run the following i get an error message. I need to be able to insert dates as yyyy-mm-dd hh:mm:ss.
Many Thanks
Create table mfSitPosition
(
sitPosition int,
sitDateTime smalldatetime,
)
Go
Insert into mfSitPosition values (1234,2010-11-28 18:30:00 )
Many Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

p.stevens76 (11/28/2010)
Hi
can anyone tell me why when i run the following i get an error message. I need to be able to insert dates as yyyy-mm-dd hh:mm:ss.
Many Thanks
Create table mfSitPosition
(
sitPosition int,
sitDateTime smalldatetime,
)
Go
Insert into mfSitPosition values (1234,2010-11-28 18:30:00 )
Many Thanks

First, the date needs to be quoted like the following...

Insert into mfSitPosition values (1234,'2010-11-28 18:30:00')

Second, be really, really careful. Make sure that you're storing the dates in a DATETIME column and not a VARCHAR column. You're opening yourself up to a world-of-hurt and some real performance problems if you store dates and/or times as a VARCHAR.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

LoosinMaMind Hall of Fame Points: 3366 More actions · Answer 2

Many Thanks, I posted a thread a couple of days ago about this but i think i over complicated the matter. Have a look if you want, you will see what i'm try to achieve ( i think??)

That has work fine, i now need to run a query against that date field to see if the date in the field is less than 30 minutes ago from the time the query is ran. Would i use the datediff function or is there another way???

If you can help, thaks in advance.

LutzM SSC Guru Points: 107049 More actions · Answer 3

Something like this?

SELECT sitPosition

FROM mfSitPosition

WHERE sitDateTime >DATEADD(MI,-30,GETDATE())

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

LoosinMaMind Hall of Fame Points: 3366 More actions · Answer 4

Hi, many thanks for that, works fine.

i now need to put something in place so that if the date is >= 30 mins ago then a stored proc will run, if the date is within the 30 min period then no action.

i have the stored proc ready, it is the dates i was struggling with and the structure of whole process.

still fairly new to all this and am at a stage where i know what is possible but need examples to see how it all fits together.

Many thanks in advance

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

p.stevens76 (11/28/2010)
Hi, many thanks for that, works fine.
i now need to put something in place so that if the date is >= 30 mins ago then a stored proc will run, if the date is within the 30 min period then no action.
i have the stored proc ready, it is the dates i was struggling with and the structure of whole process.
still fairly new to all this and am at a stage where i know what is possible but need examples to see how it all fits together.
Many thanks in advance

It's time to introduce you to Books Online. Take a look at "IF"...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions