Converting date VARCHAR to DATETIME

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

SQL Server 2005 Forums

Transact-SQL (2005)

Converting date VARCHAR to DATETIME

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-09-22 : 05:56:59

Hi

I want to convert a varchar field to datetime:

declare @date varchar(8)
set @date = '20080101'

select @date = convert(varchar(8),convert(datetime,@date,112))--112: yyyymmdd
select @date

Current Result = 'Jan 1 2'
Desired Result = '20080101'

Thanks

Hearty head pats

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 06:01:33

why again converting to varchar?

just give select convert(datetime,@date) to convert to datetime. the required formatting can be done at your front end application.
Also its always better to use proper datatype for varaibles. Was there any special reason behind declaring @date variable as varchar?

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-09-22 : 06:20:05

Hi

In my example, I replaced the attribute from the table to a variable for examples sake.

The actual system I'm using is a legacy database, therefore, I have no idea why varchar was chosen as the data type for the field. However, there is no possibility of changing it to datetime.

Also, I have to change back to varchar, as the ultimate aim is to add another week onto the date, therefore, persist it back to the same attribute. This has to be done in the DB - this is not a process being used by the front end.

Thanks

Hearty head pats

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 06:36:18

quote:
Originally posted by Bex

Hi

In my example, I replaced the attribute from the table to a variable for examples sake.

The actual system I'm using is a legacy database, therefore, I have no idea why varchar was chosen as the data type for the field. However, there is no possibility of changing it to datetime.

Also, I have to change back to varchar, as the ultimate aim is to add another week onto the date, therefore, persist it back to the same attribute. This has to be done in the DB - this is not a process being used by the front end.

Thanks

Hearty head pats

if you have manipulations to be done on date its always better to keep it in datetime datatype as you can make use date functions for manipulation.

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-09-22 : 06:46:29

I completely agree. This database is a bit of a nightmare. All the table names mean pretty much nothing.... (customer = 'fhap0cus'??????) and the datatype choices are bizarre.... I think this is something to do with the original system the DB resided on.

Anyway, don't suppose you have any other suggestions?

Basically I'm trying to test an SSIS package, and need to fudge some test data generated sometime ago. Hence the need to add on another couple of weeks.

Hearty head pats

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 06:51:10

[code]declare @date varchar(8)
set @date = '20080101'

select @date = convert(varchar(8),dateadd(wk,1,convert(datetime,@date,112)),112)--112: yyyymmdd
select @date[/code]

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-09-22 : 07:41:25

Thankyou

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources