Calculate date from string: "Datevalue" equivalent for T-SQL

[Chuck]

Hi,

Is there a T-SQL equivalent to the Microsft Access "Datevalue" function?
This function returns the date from a string e.g. MyDate =
Datevalue("1-APR-2004").

Thanks

[Tom Moreau]

Check out CONVERT() in the BOL.
--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql


"Chuck" <***@no.where> wrote in message news:e5T$***@tk2msftngp13.phx.gbl...
Hi,

Is there a T-SQL equivalent to the Microsft Access "Datevalue" function?
This function returns the date from a string e.g. MyDate =
Datevalue("1-APR-2004").

Thanks

[Aaron Bertrand - MVP]

See http://www.aspfaq.com/2464 for styles you can use with convert.

I was surprised to see this work:

SELECT CONVERT(SMALLDATETIME, '1-APR-2004', 105)

Because of this:

SELECT CONVERT(CHAR(10), GETDATE(), 105)
----------
28-05-2004
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

Post by Chuck
Hi,
Is there a T-SQL equivalent to the Microsft Access "Datevalue" function?
This function returns the date from a string e.g. MyDate =
Datevalue("1-APR-2004").
Thanks

[Alexander Makarov]

or SELECT cast( '1-APR-2004' as SMALLDATETIME)
--
Thank you,

Alex

Post by Aaron Bertrand - MVP
See http://www.aspfaq.com/2464 for styles you can use with convert.
SELECT CONVERT(SMALLDATETIME, '1-APR-2004', 105)
SELECT CONVERT(CHAR(10), GETDATE(), 105)
----------
28-05-2004
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

Post by Chuck
Hi,
Is there a T-SQL equivalent to the Microsft Access "Datevalue" function?
This function returns the date from a string e.g. MyDate =
Datevalue("1-APR-2004").
Thanks

[Aaron Bertrand [MVP]]

Yes, I realized after I posted that I could have left the style number out.

SELECT CONVERT(SMALLDATETIME, '1-APR-2004')

I tend to stick with CONVERT for [SMALL]DATETIME as opposed to CAST because
I often want to use the numeric style parameter (which doesn't apply to
CAST).