Date Issues - 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 2005 Forums

Transact-SQL (2005)

Date Issues

Author

Topic

jdonald
Starting Member

3 Posts

Posted - 2007-03-28 : 11:33:40

How do I convert a date?? Our database has dates, datetimes and really out of the ordinary dates. This one is like this:
20010301??? I tried:
convert(char(10), cast(c.date_of_service as datetime), 101) as DOS but when I go to search this colum using:
WHERE (c.begin_date_of_service BETWEEN '3/1/2007' AND '3/28/2007') it comes back with NOTHING, the big ZERO. But if I run it with the date as WHERE (c.begin_date_of_service BETWEEN '20070301' AND '20070328') I get all the information I need. Now I can't have the date like this as I am trying to insert this into Crystal Reports so other people can run this report instead of me, PLEASE HELP. Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 11:37:50

You write DATE, but how is the information stored?
INT? VARCHAR? DATETIME?

Peter Larsson
Helsingborg, Sweden

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-03-28 : 11:38:26

What is the datatype of the column in the table?

************************
Life is short. Enjoy it.
************************

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-03-28 : 11:45:07

It looks like you must have the data stored as CHAR or VARCHAR datatype.

This selection criteria would only work with a datetime datatype:

WHERE (c.begin_date_of_service BETWEEN '3/1/2007' AND '3/28/2007')

If your dates are stored as character strings in format YYYYMMDD, then your query will have to be the same:

WHERE (c.begin_date_of_service BETWEEN '20070301' AND '20070328')

As you may know, it is a very bad practice to store dates in CHAR or VARCHAR datatypes.

CODO ERGO SUM

jdonald
Starting Member

3 Posts

Posted - 2007-03-28 : 11:45:41

It's stored as an INT.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 11:48:01

There you go!

Use "ISO style" as YYYYMMDD, and you are free! But this is still a bad table design.
You should consider changing the column to datetime.

Peter Larsson
Helsingborg, Sweden

jdonald
Starting Member

3 Posts

Posted - 2007-03-28 : 11:52:38

I can't this is our company EMR program I am stuck with it this way.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 11:53:45

Can you create a calculated column with DATETIME datatype?


ALTER TABLE Table1
ADD DT AS DATEADD(MONTH, 12 * (Col1 / 10000) - 22801 + (Col1 % 1000) / 100, Col1 % 100 - 1)

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 11:56:45

Like this?

DECLARE @YMD INT

SELECT	@YMD = 20070328

SELECT	@YMD,
	DATEADD(MONTH, 12 * (@YMD / 10000) - 22801 + (@YMD % 1000) / 100, @YMD % 100 - 1) -- This line

Peter Larsson
Helsingborg, Sweden

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-03-28 : 12:50:19

Since your "date" field is already an int, you convert the dates you want to use as a range into an integer and then run your query. Somethint like:

SELECT CAST(CONVERT(VARCHAR, CURRENT_TIMESTAMP, 112) AS INT)

-Ryan

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-03-28 : 13:25:56

quote:
Originally posted by jdonald

I can't this is our company EMR program I am stuck with it this way.

I feel your pain.

Well, not really, but good luck.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources