getting variable into datetime format for MS SQL

TechBoyJK

Lifer
Oct 17, 2002
16,699
60
91
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

Diamond Member
Mar 18, 2003
5,513
0
0
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.
 

WannaFly

Platinum Member
Jan 14, 2003
2,811
1
0
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

Lifer
Oct 17, 2002
16,699
60
91
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

Platinum Member
Jan 14, 2003
2,811
1
0
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.