Date Conversion - 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

Date Conversion

Author

Topic

Baz_Singh
Starting Member

5 Posts

Posted - 2010-09-15 : 06:46:34

Hi,

I have a varchar with a value such as '07 2010' held for the field 'Month&Year' in my table 'tblTest'. I need to convert this to a date field such as 01-07-2010.

Many Thanks

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-15 : 06:57:06

DECLARE @var VARCHAR(10)
DECLARE @dd VARCHAR(2)
DECLARE @mm VARCHAR(2)
DECLARE @yyyy VARCHAR(4)
DECLARE @final VARCHAR(10)
SET @var='07 2010'

SET @mm = LEFT(@var,2)
SET @yyyy=RIGHT(@var,4)

SET @final= '01-'+@mm+'-'+@yyyy

PRINT (@final)

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-15 : 06:57:23

matty
Posting Yak Master

161 Posts

Posted - 2010-09-15 : 07:00:14

declare @p varchar(20)
set @p ='07 2010'
select CONVERT(date, RIGHT(@p,4)+ LEFT(@p,2) + '01')

Note: Use datetime datatype for verison 2000/2005

Baz_Singh
Starting Member

5 Posts

Posted - 2010-09-15 : 07:07:24

Thanks a million guys :) both do what i was initially trying to do but the suggestion by Matty enables me to push the boat a little further :)

Thanks again

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-15 : 09:16:11

Also make sure to have a new column with proper DATETIME datatype and update values to that column and use that column further

Madhivanan

Failing to plan is Planning to fail

Baz_Singh
Starting Member

5 Posts

Posted - 2010-09-16 : 03:40:09

cheers madhivanan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-21 : 10:21:15