How to increase dates in nvarchar format

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

How to increase dates in nvarchar format

Author

Topic

gomad2000
Starting Member

9 Posts

Posted - 2011-05-20 : 18:30:36

I am looking help to keep a demo database up to date. I have a number of different tables each have dates in nvarchar format '201103301349' ie year month day hour minute. I want to be able to increase all the dates by 7 days so that the demo is always uptodate each week. I am pretty new to SQL and I am using SQL Express 2008.

If you don't ask ....

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-20 : 22:05:52

convert the column to a datetime, add 7 days, convert back to varchar format for the update.
something like
update tbl set fld = replace(replace(replace(convert(varchar(19),dateadd(dd,7,left(fld,8) + ' ' + substring(fld,9,2) + ':' + substring(fld,11,2) + ':' + right(fld,d)),19),' ',''),':',''),':','-')

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-20 : 22:44:45

you can simplified a bit by not touching the time portion


set fld = convert(varchar(8), dateadd(day, 7, convert(datetime, left(fld, 8), 11)), 112) + right(fld, 8)

KH
[spoiler]Time is always against us[/spoiler]

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-05-21 : 03:03:49

quote:
Originally posted by khtan

you can simplified a bit by not touching the time portion

set fld = convert(varchar(8), dateadd(day, 7, convert(datetime, left(fld, 8), 11)), 112) + right(fld, 8)

KH
[spoiler]Time is always against us[/spoiler]

Slightly shorter code done by removing the explicit cast inside the dateadd:

set fld = convert(varchar(8),dateadd(day,7,left(fld,8)),112)+right(fld,8)

CODO ERGO SUM

gomad2000
Starting Member

9 Posts

Posted - 2011-05-21 : 05:30:58

Thanks guys thats nearly it.
The only thing is that it seems to add 4 additional charachters for Michaels and KHTan's. So my date for example goes from '201101131939' to '2011012001131939'
Nigel i get an error with your code with a query on the final d of 'right(fld,d)),19)' Should that be a number?

If you don't ask ....

gomad2000
Starting Member

9 Posts

Posted - 2011-05-21 : 05:36:46

It's OK I changed the right(fld,8) to right(fld,4) and it worked beautifully.
Many thanks.

If you don't ask ....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-21 : 06:20:36

yes. it should be right(fld, 4), my mistake

KH
[spoiler]Time is always against us[/spoiler]

gomad2000
Starting Member

9 Posts

Posted - 2011-05-21 : 09:35:29

I have come acorss another date format that I need to increase by 7. Again it is nvarchar but looks like '13/01/2011' ie dd/mm/yyy

When I use this
update tbl set fld = convert(varchar(10),dateadd(day,7,left(fld,10)),103)

I get a date like '6/01/2011' returned as '8/06/2011' and if I have a date like '13/01/2011' I get an error 'The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.'
It seems to swap the day and month or something.

If you don't ask ....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-22 : 05:03:18

the query we posted earlier is for date in YYYMMDDHHMM format.

If your date string is in DD/MM/YYYY, you need to convert to datetime data type using style 103 and then add 7 days to it before converting back to string

this will convert to datetime and add 7 days to it


dateadd(day, 7, convert(datetime, '13/01/2011', 103))

is this date in DD/MM/YYYY format on the same column ?

You should really consider using datetime data type for the date & time instead of varchar. It will saved you lots of trouble

KH
[spoiler]Time is always against us[/spoiler]

gomad2000
Starting Member

9 Posts

Posted - 2011-05-24 : 06:55:46

Having read some articles on SQL dates I totally agree with you KH but I am notthe designer of the database and so have to deal with what I have in front of me. Your solution worked a treat. Thnaks again.

===========================
If you don't ask ....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources