Problem converting varchar to date

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

Problem converting varchar to date

Author

Topic

ZoeyElle
Starting Member

4 Posts

Posted - 2011-04-04 : 21:49:27

Hello,

I'm new to sql. Using ms sql server mgmnt studio to perform queries.

Dates are stored as varchar(5) in the table by ppl who creted the table :(
When I query the field, it returns 10076 as result. However, this same field is displayed as 17/03/10 at the frontend system.

I need to display 10076 as a proper date in the query result as i will need to do a date comparison.

Tried convert....but it didn't work. Error : conversion failed when converting datetime from character string.

Urgent help is much needed.

I only have read access to the tables....so I cant change the datatype.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2011-04-05 : 00:08:03

try this:


declare @date varchar(5)
set @date = 10076

print convert(datetime,cast(@date as int),102)

ZoeyElle
Starting Member

4 Posts

Posted - 2011-04-05 : 02:38:17

Hi slimt,

Thanks for the quick response.

Tried your code. DidnT return an error.
But the returned idate is Aug 4 1927 12:00 AM not 17/03/2010 tho.
Any idea how to proceed from here?

Thanks.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2011-04-05 : 03:19:07

You need to know how the number interpreted by the front end. Is it a number of days since a starting point? If so when?
What kind of date comparison do you need to do? Maybe you don't have to convert it at all.
And finally don't store dates as anything except dates! (I know this is not your design but it's a good example).

McDebil
Starting Member

23 Posts

Posted - 2011-04-05 : 04:26:53

Hi ZoeyElle

The first 2 characters is the year part of the date and the last 3 characters is the number of days.
Try use this:

DECLARE @InputDate VARCHAR(5)
DECLARE @Yearpart VARCHAR(10)
DECLARE @DayPart INTEGER

SET @InputDate = '10076'
SET @Yearpart = SUBSTRING(@InputDate,1,2) + '-01-01'
SET @DayPart = CAST(SUBSTRING(@InputDate,3, 3) AS INTEGER) - 1

SELECT DATEADD(dd,@DayPart,@Yearpart)

Result: 2010-03-17 00:00:00.000 (You can format this ANSI date to US date format)

McDebil

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-04-05 : 07:41:27

See if this helps too
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82164

Madhivanan

Failing to plan is Planning to fail

ZoeyElle
Starting Member

4 Posts

Posted - 2011-04-06 : 03:19:58

Hi everyone,
Thanks for the posts.

Hi McDebil,
Thanks for the explanation on the date representation.
I tried your code but i got different result.
Result returned was 2001-12-15 00:00:00.000.

However if i feed the dateadd function like so:
SELECT DATEADD(dd, @DayPart, '2010-01-01'). Then i get 2010-03-17 00:00:00.000.

The @Yearpart not being passed correctly?
Appreciate your help.

Thanks.

ZoeyElle
Starting Member

4 Posts

Posted - 2011-04-06 : 03:26:27

Hi McDebil,

@Yearpart value is 10-01-01 instead of 2010-01-01. That's why the different result.
My bad...

Thanks again for your help!

Cheers,
ZoeyElle

McDebil
Starting Member

23 Posts

Posted - 2011-04-06 : 03:47:57

The DATEADD function manage the old 2 digit year codes.

From 00 to 49 is the 21. century and from 50 to 99 is the 20. century.

McDebil

McDebil
Starting Member

23 Posts

Posted - 2011-04-06 : 04:28:23

quote:
Originally posted by ZoeyElle

Hi everyone,
Thanks for the posts.

Hi McDebil,
Thanks for the explanation on the date representation.
I tried your code but i got different result.
Result returned was 2001-12-15 00:00:00.000.

This caused by the different datetime formats. Your server parse the @Yearpart a MM-DD-YY, my server parse as YY-MM-DD.
The correction look at end this reply

However if i feed the dateadd function like so:
SELECT DATEADD(dd, @DayPart, '2010-01-01'). Then i get 2010-03-17 00:00:00.000.

The @Yearpart not being passed correctly?
Appreciate your help.

Thanks.

DECLARE @InputDate VARCHAR(5)
DECLARE @Yearpart VARCHAR(10)
DECLARE @DayPart INTEGER

SET @InputDate = '10076'

SET @Yearpart = SUBSTRING(@InputDate, 1, 2)
SET @Yearpart = CASE WHEN CAST(@Yearpart AS INTEGER) < 50
THEN '20' + @Yearpart + '-01-01'
ELSE '19' + @Yearpart + '-01-01'
END

SET @DayPart = CAST(SUBSTRING(@InputDate, 3, 3) AS INTEGER) - 1

SELECT DATEADD(dd, @DayPart, @Yearpart)

McDebil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources