Display 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

General SQL Server Forums

New to SQL Server Programming

Display Date

Author

Topic

Maachie
Yak Posting Veteran

69 Posts

Posted - 2007-11-30 : 08:49:41

How would I display just the date in the dataset if I have the data with date and time like this 2007-05-02 07:14:48.000
I want to truncate/or something to the time and just display the date. How would I change my query.

SELECT usr_end_date,date_exp,usr_last_name, usr_first_name, usr_login,employeeid, displayname
FROM [OIM_FIX].[dbo].[OIM_USR] a, CORP_EMP_IDs b
WHERE USR_login = CAST(b.employeeid AS varchar)and
usr_end_date <> date_exp

currently it is returning the full date and time like this
2007-05-02 07:14:48.000
I want just the date like this 2007-05-02

I am using sql server 2005

Thanks,
Maachie

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-30 : 08:56:34

[code]convert(varchar(10), usr_end_date, 120)[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Maachie
Yak Posting Veteran

69 Posts

Posted - 2007-11-30 : 09:04:47

In addition, the main goal is to see all the Users who's End_Dates doesn't match. The two date columns are used to compare. currently if the date is matched and the time is not matched that shows in the result set becoz we want to see all the Users who's End_Dates doesn't match. But if we truncate the time. There are some dates still match.

for example before truncating the time:
usr_end_date date_exp
2008-02-03 14:24:56.000 2008-02-03 04:52:51
2008-02-06 16:54:13.000 2008-02-06 10:00:00
2008-02-06 17:02:27.000 2008-05-10 04:52:51
2008-02-07 11:17:04.000 2007-11-28 04:52:51
2008-02-11 15:16:45.000 2008-02-11 10:00:00

After truncating it would show like this:
2/3/2008 2/3/2008
2/6/2008 2/6/2008
2/6/2008 5/10/2008
2/7/2008 11/28/2007
2/11/2008 2/11/2008

but we want only that does not match
Like this:
2/6/2008 5/10/2008
2/7/2008 11/28/2007

So please help me to compare two date fields. I would much appreciate your help. Thanks, Maachie

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-30 : 09:08:50

[code]SELECT convert(varchar(10), usr_end_date, 120) as usr_end_date,date_exp,usr_last_name, usr_first_name, usr_login,employeeid, displayname
FROM [OIM_FIX].[dbo].[OIM_USR] a, CORP_EMP_IDs b
WHERE USR_login = CAST(b.employeeid AS varchar)and
dateadd(day, datediff(day, 0, usr_end_date), 0) <> dateadd(day, datediff(day, 0, date_exp), 0)[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Maachie
Yak Posting Veteran

69 Posts

Posted - 2007-11-30 : 09:41:14

Hi Harsh,

Thank you so much. The number of result came out to be the same. but more date formatting the second time. Thanks very much.

Since I am still a beginner,
for my understanding can you explain how these date function code work?
1. convert(varchar(10), usr_end_date, 120)
2. dateadd(day, datediff(day, 0, usr_end_date), 0)

Also, I tried this but did not work. Is it because i used sql server 2005?
To_char(usr_end_date, 'MM/DD/YYYY') <> To_char(date_exp, 'MM/DD/YYYY')

Thanks very much again,
Maachie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources