Problem: Coverting varchar to Datetime

Question

Post reply

Problem: Coverting varchar to Datetime

swatichari_itx

SSC Veteran

Points: 220
More actions
March 6, 2008 at 7:20 am

#184734

hi,
i have a table name emp_dd wherein there is a feild name month_yr(nvarchar) stores data in 'mon/year' format e.g 'Jan/2008'.
I want to get the latest mon/year value from this table.
I am running the following query
select distinct month_yr,convert(datetime,right(month_yr,4)+
CASE LEFT(month_yr,3)
WHEN 'JAN' THEN '-01-01'
WHEN 'FEB' THEN '-02-01'
WHEN 'MAR' THEN '-03-01'
WHEN 'APR' THEN '-04-01'
WHEN 'MAY' THEN '-05-01'
WHEN 'JUN' THEN '-06-01'
WHEN 'JUL' THEN '-07-01'
WHEN 'AUG' THEN '-08-01'
WHEN 'SEP' THEN '-09-01'
WHEN 'OCT' THEN '-10-01'
WHEN 'NOV' THEN '-11-01'
ELSE '-12-01'
END,101) from emp_dd
so that i can fetch the latest mon/year.
But it gives the following error.
'Syntax error converting datetime from character string.'
Is there any solution for this.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 1

No... you don't need anything that complicated. Looky here...

[font="Courier New"]SELECT CAST('Jan 2008' AS DATETIME)

------------------------------------------------------

2008-01-01 00:00:00.000[/font]

So, the only thing you need to do is change the backslash to a space...

[font="Courier New"]SELECT CAST(REPLACE('Jan/2008','/',' ') AS DATETIME)[/font]

Get it? Now all you need to do is replace the hardcode date with a column name and add a FROM clause and you're done.

[font="Courier New"] SELECT DISTINCT CAST(REPLACE(Month_Yr,'/',' ') AS DATETIME) AS Month_Yr

FROM emp_dd[/font]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Giri Duddu SSCommitted Points: 1638 More actions · Answer 2

hi........... try the below querry

select distinct month_yr,

convert(datetime,'01/' + month_yr,101) from emp_dd

swatichari_itx SSC Veteran Points: 220 More actions · Answer 3

Nice solutions but

I tried the both the quries

SELECT DISTINCT CAST(REPLACE(Month_Yr,'/',' ') AS DATETIME) AS Month_Yr

FROM emp_dd

select distinct month_yr,

convert(datetime,'01/' + month_yr,101) from emp_dd

this also gives me the same error message

Syntax error converting datetime from character string.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 4

The only way either of those would fail is if the data is different that what you said. Please post some sample data and a table to load it in using the techniques discussed in the URL in my signature line.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

You have bad data in your [font="Courier New"]month_yr[/font] column.

FYI, varchar based date/time columns virtually always have bad data in them.

If you want to find them, try using:

SELECT * FROM emp_dd WHERE not IsDate( Replace(month_yr, '/', ' ') )

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 6

rbarryyoung (3/8/2008)
FYI, varchar based date/time columns virtually always have bad data in them.

Nary a truer word spoken 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

swatichari_itx SSC Veteran Points: 220 More actions · Answer 7

Thank you Guys.

The problem has been solved.

Your doubt was very correct .

In table emp_dd is month_yr column is carrying on bad data

due to which it was giving the error.

'Syntax error converting datetime from character string. '

There is one value 'Jun/20O6' in this value seventh character is 'O' letter instead of Zero(0).

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

The insidious evil of character-based dates is thus exposed.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]