getting variable into datetime format for MS SQL

[TechBoyJK]

Hi, i have a date variable that i take on a form. it is for a birthday. so the variable is birthday, yyyy, mm, dd set in a varchar field. i was using createodbcdatetime to get it into a "datetime" format... but MS SQL isn't letting me insert this value into the regular datetime datatype. How do i need to convert the original string into the datetime, preferably smalldatetime, so that it will be accepted by MS SQL? thanks! (im googling too)

 

[kamper]

You're formulating sql commands with text? I've done that, but only with jdbc, which takes a nice java.util.Date and inserts it correctly for you. Do you have the reference manual for sql server handy? It should be a help file that came with the installation and should give you a pretty direct answer as to the formatting.

 

[kamper]

Here's the product of 2 minutes of googling: http://www.databasejournal.com/features/mssql/article.php/2191631

 

[WannaFly]

I'm not sure of your question - whats the exact input format? yyyy,mm,dd?? if so you'll probably have to do some sort of replace/substring, i.e.
convert(datetime, replace(@date,',',''))
or
convert(datetime, substring(@date,1,4)+substring(@date,6,2)+substring(@date,9,2))

not sure if this is what you are looking for...

 

[TechBoyJK]

Originally posted by: WannaFly
I'm not sure of your question - whats the exact input format? yyyy,mm,dd?? if so you'll probably have to do some sort of replace/substring, i.e.
convert(datetime, replace(@date,',',''))
or
convert(datetime, substring(@date,1,4)+substring(@date,6,2)+substring(@date,9,2))

not sure if this is what you are looking for...

the string looks like this yyyy-mm-dd I can always change it because the different elements, aka yyyy and mm, are taken individually in drop down select boxes... aka month returns a value of 1-12.... day returns value of 1-31, year 1900-2005.. so i need to combine these different fields into one variable and format it so that it will be accepted by SQL server for datetime.. preferably small datetime..

 

[WannaFly]

Originally posted by: TechBoyJK

Originally posted by: WannaFly
I'm not sure of your question - whats the exact input format? yyyy,mm,dd?? if so you'll probably have to do some sort of replace/substring, i.e.
convert(datetime, replace(@date,',',''))
or
convert(datetime, substring(@date,1,4)+substring(@date,6,2)+substring(@date,9,2))

not sure if this is what you are looking for...

the string looks like this yyyy-mm-dd I can always change it because the different elements, aka yyyy and mm, are taken individually in drop down select boxes... aka month returns a value of 1-12.... day returns value of 1-31, year 1900-2005.. so i need to combine these different fields into one variable and format it so that it will be accepted by SQL server for datetime.. preferably small datetime..

yyy-mm-dd will go into smalldatetime, just do convert(smalldatetime,'2005-01-15') and it'll work.