Date format Question

Question

Post reply

Date format Question

Bill Kana

SSC Rookie

Points: 49
More actions
October 20, 2006 at 11:07 am

#174719

I have a datetime field in one of my tables that I am using to pass to SQL Report Server. The field is called ActivityDate and the table is called EducationHistory. The ActivityDate field's data is in the 10/20/2006 12:00:00 AM format and I need the output to be either 10/20/2006 or 10/20/06.
I will be using this field as a parameter in Reporting Services and it would be a lot easier to have the users type in a simple date such as 10/20/06.
Thanks in advance, B

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 1

Is this what u want:

declare @dt datetime

set @dt ='10/20/2006 12:00:00 AM'

select @dt, Convert(Char(10),@dt,101),Convert(Char(10),@dt,1)

Thanks

Sreejith

Bill Kana SSC Rookie Points: 49 More actions · Answer 2

Not quite sure, I am a novice SQl user, so bare with me. I basically need to drop the time portion of the field. What are you referring to when you use @dt. Also, wouldn't your set statement make the time appear. Maybe I'm missing something, could you go into further detail? Thx

Ken McKelvey SSCoach Points: 18843 More actions · Answer 3

Sreejith is pointing out that DATETIME is just a number and how you see it formatted depends on a number of settings.

To explicitly format the DATETIME in US format, you need to use the CONVERT function with a style of either 1 or 101.

(You can see the detail on the CONVERT function in BOL - SQL Server Books Online.)

eg

SELECT CONVERT(CHAR(10), ActivityDate, 101) AS YYYYDate

,CONVERT(CHAR(8), ActivityDate, 1) AS YYDate

-- This will also work if you default to US settings

,CAST(ActivityDate as CHAR(10)) AS YYYYDate2

FROM EducationHistory

To search for rows with a given activity date you should do a range search to account for differenct times.

eg

SELECT *

FROM EducationHistory

WHERE ActivityDate >= CONVERT(DATETIME, '10/20/06', 1)

AND ActivityDate < DATEADD(day, 1, CONVERT(DATETIME, '10/20/06', 1))

or

SELECT *

FROM EducationHistory

WHERE ActivityDate >= CONVERT(DATETIME, '10/20/06', 1)

AND ActivityDate < CONVERT(DATETIME, '10/20/06', 1) + 1

Bill Kana SSC Rookie Points: 49 More actions · Answer 4

Thank you for the information....it was very helpful. I was able to solve my issue!