char to date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

char to date

Author

Topic

siumui
Yak Posting Veteran

54 Posts

Posted - 2012-04-05 : 15:47:56

Hello all.

I have a varchar column Shipment which contains date in there, for example: SHIPMENT_03202011

I use SUBSTRING and get the date portion out to a new column name Ship_Date.
How do I convert what's in Ship_Date to date format yyyymmdd and be able to use what's in Ship_Date to compare to another date (yyyymmdd)?

Thank you

siumui

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-05 : 15:50:52

you just need to make new column of type datetime and pass dates in format YYYYMMDD using CONVERT. then it will get stored as native date value itself and you dont really need to worry on format anymore

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

siumui
Yak Posting Veteran

54 Posts

Posted - 2012-04-05 : 16:05:52

quote:
Originally posted by visakh16

you just need to make new column of type datetime and pass dates in format YYYYMMDD using CONVERT. then it will get stored as native date value itself and you dont really need to worry on format anymore

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I use
CONVERT(datetime, Ship_Date, 112) and it gives me error, "The conversion of a varchar data type to a datetime data type resulted in an out-or-range value."

The Ship_Date column is in a temp table.

siumui

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-05 : 16:19:13

that wont work as your current format is mmddyyyy

so if you want to convert it datetime

use

CONVERT(datetime,STUFF(STUFF(Ship_Date,3,0,'/'),6,0,'/'), 101)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

siumui
Yak Posting Veteran

54 Posts

Posted - 2012-04-05 : 16:36:52

quote:
Originally posted by visakh16

that wont work as your current format is mmddyyyy

so if you want to convert it datetime

use

CONVERT(datetime,STUFF(STUFF(Ship_Date,3,0,'/'),6,0,'/'), 101)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I use your conversion statement and it gives me "Conversion failed when converting date and/or time from character string."

Please help. Thanks

siumui

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-05 : 18:42:03

then i'm sure your Ship_date is not having dates in consistent format

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources