CODEWITHSUNDEEP
sql-server
user1296762
user1296762

Reputation: 512

VARCHAR TO DATE

Currently i have a column called date as varchar instead of date time. the format is currently 'sep12'. How can i transfer this to a date type? I know this is the template but I'm not sure what to change the numbers to due to my current format as MMMYY.

CAST (
    SUBSTRING(date, 3, 2) + '/' +
    SUBSTRING(date, 1, 2) + '/' +
    SUBSTRING(date, 5, 4) 
AS DATETIME)

any help would be greatly appreciated

At the moment when ordering by the date field it is ordering like apr09, apr10. aug09, aug10 but I need it to be in year order

Upvotes: 2

Views: 112

Answers (1)

Taryn
Taryn

Reputation: 247710

First, storing dates in this manner is a terrible idea. But if you know that the year will always start with 20 then you can use something like this (see SQL Fiddle Demo):

declare @dt varchar(10) = 'Sep12'

select convert(datetime, left(@dt, 3) + '01 20' + right(@dt, 2), 100)

If pulling data from your table:

select convert(datetime, left(yourDateField, 3) + '01 20' + right(yourDateField, 2), 100)
from yourTable

Upvotes: 2

Related Questions