Modifying a char date - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Modifying a char date

Author

Topic

r.horn@proms.nl
Starting Member

5 Posts

Posted - 2007-01-01 : 22:38:13

I have a field in a table that stores a date as a char. (e.g. 02-01-2007 for Jan. 2nd 2007). I need a query to increase all the years by one. (so 2006 should become 2007, 2007 should become 2008 and so on).

I tried working with CONVERT and DATEADD but I can't seem to figure it out. Is there anyone that could point me in te right direction, I'm not really experienced in these things

Rene

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-01 : 22:45:08

This one?

Update Tbl1
Set DateCol = convert(varchar(10), dateadd(year, 1, convert(datetime, DateCol ,105)), 105)

Though, I will not recommend storing dates in varchar/char/nvarchar types.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

r.horn@proms.nl
Starting Member

5 Posts

Posted - 2007-01-01 : 23:51:07

That's the one. Thank you so very much for helping me out.

r.horn@proms.nl
Starting Member

5 Posts

Posted - 2007-01-01 : 23:57:47

BTW I choose the char approach for my dates because I had a lot of problems in the past with language settings on the servers and clients and I never knew 100% shure how a date was interpreted (dd/mm/yyyy or mm/dd/yyyy). All probles are solved since I use the char dates as I store them in the table exactly the way I want it no matter wat the international settings of the server, the client or even odbc drivers are it now always works as expected. Probably there would be a better solution but I 'don't really know what the disadvantages are of using a char date instead of a datetime. (except for the fact that I'm not familiar with the CONVERT statement of course :-) )

miranwar
Posting Yak Master

125 Posts

Posted - 2007-01-02 : 04:40:12

The date format for SQL server is in U.S. date format MM/DD/YY, unless a localized version of SQL Server has been installed. The date format is dependent on the the SQL server language settings. if the default language is US_ENglish the date will always be stored as MM/DD/YYYY. You can check the default language with this:

select name ,alias, dateformat
from syslanguages
where langid =
(select value from master..sysconfigures
where comment = 'default language')

Kristen
Test

22859 Posts

Posted - 2007-01-02 : 05:28:49

"I don't really know what the disadvantages are of using a char date instead of a datetime"

SQL Server will be happy with "string" dates provided that you provide them in "yyyymmdd" format, or a native Date or DateTime format - e.g. a VB variable using the Date datatype.

You will then be storing your date data in a DateTime datatype column, and all the date manipulation, sorting, difference-ing, etc. will become trivial. Plus you will be passing them to your application in a DateTime variable, which avoids parsing and is completely unambiguous.

SELECT Col1, Col2, ...
FROM MyTable
ORDER BY MyCharDate

is going to be sorted by Month, Day and then Year in your method. Whereas

SELECT Col1, Col2, ...
FROM MyTable
ORDER BY MyDateDatatypeColumn

is going to be in chronological order.

String dates use a few more bytes than DateTime

On the downside there is no DATE datatype in SQL Server, so you have to consider he fact that it stores a TIME too. If you only ever provide YYYYMMDD dates then the time will be "00:00:00.000" and you can probably just ignore it. However, if you want to use GetDate() to compare using the current time - "All WIDGETS in last day/week/..." then you have to fiddle with the return from GetDate() to "round" it to a whole day.

I think its a big shame that DATE datatype wasn;t added in SQL 2005 ... but ...

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-02 : 06:36:55

1 What is the front end application you are using?

2 When you say " I had a lot of problems in the past with language settings on the servers and clients", explain the problem

3 Use always DATETIME datatype and if you want to show dates in different formats in front end use its Format function. Also when you send date value from client to server, you should use Date varable in front end and assign date value in "YYYYMMDD HH:MM:SS" or "YYYY-MM-DD HH:MM:SS" format which is unambigious

4 Your problem will be solved if the consider those methods including what Kristen said

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-02 : 06:43:35

Why would you like to do this?
What happens with dates for February 29?

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-01-02 : 09:59:57

"What happens with dates for February 29?"

Easiest would be to migrate to mySQL:

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html

I like the final bit about converting "zero" dates to NULL because ODBC cannot handle "zero" dates ... enough said!

Kristen

r.horn@proms.nl
Starting Member

5 Posts

Posted - 2007-01-02 : 17:37:40

madhivanan, the frontend app is a kind of customer loyalty card system. The has been a programming error where the LastUpgraded date got mixed up with the ExpirationDate so I needed this query to solve this in an instance (15000 loyalty cards). Further more I had the following problems in the past (not anymore since I use the chat dates). I have one SQL Server, I have multiple clients (XP, 2000 (and even 98 in te not so long ago past)) and numerous web clients around the globe on 2 different websites one ASP based and one PHP based (new ASP.NET is beiing created). The problem was that sometimes dates got mixed up. A date of November 1st became January eleventh. This is the fault of the client but I don't need that kind of problems if you know what I mean. Someone advised me to use char's instead of date time and indeed I have never had these problems. I do agree that it would have been a better approache to use YYYYMMDD instead of the DD-MM-YYYY I have used now (A cool query to solve that would be nice :-) ) . Saves me 2 bytes times 5 dates per record and is more usefull for sorting and so on.

Would there be a lot of disadvantages if I use a char(8) for YYYYMMDD dates and forget about the datetime for the rest of my apps life ? You say (3) Use alwase DATETIME but I'm not completely convinced because of the above.

Further, I like MySQL but if my boss would ask me to migrate this app I would considder 2 options, a.suicide or b.find another job LOL

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-03 : 08:16:18

<<
. A date of November 1st became January eleventh.
>>
It is becuase that the client's date format is DMY but server's is MDY
If you send date from client to Server in "YYYYMMDD HH:MM:SS" ie istead of "01-11-2006" if you send "2006-11-01" it would be unambigious to Server that you are sending November 1 and not January 11

The reason why you always need DATETIME datatype is that you dont need to worry about converting it to DATETIME
If you store dates in CHAR datatype, chances are higher that INVALID dates can be stored

--Assigning Valid date
DECLARE @date_char CHAR(8), @date DATETIME
SET @date_char='20061101'
SET @date='20061101'

GO

--Assigning InValid date
DECLARE @date_char CHAR(6), @date DATETIME
SET @date_char='20063131'
SET @date='20063131'

Now @date_char will accept that that whereas @date will reject it

So you should always consider using PROPER datatype

Madhivanan

Failing to plan is Planning to fail

r.horn@proms.nl
Starting Member

5 Posts

Posted - 2007-01-12 : 10:03:59

Thank you for the help and info..

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-12 : 10:52:48

Again, I feel like a broken record here, but server and client settings for dates should NEVER matter. You should never be sending dates in any particular format from or to SQL Server. You should always be returning datetime VALUES in the proper data types. Datetime parameters going into SQL, datetime columns going out. No exceptions. If you do this, it doesn't matter what the client or server is doing internally to "format" these datetimes, they will always have the correct value and things will work fine.

Forcing formatting, passing strings in a particular format instead of using datetime values, will always make things more complicated for you and less efficient. Keep it simple -- use the correct datatypes at all times.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources