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

• Sreejith Sreedharan

  SSCertifiable

  Points: 5403

  More actions

  October 20, 2006 at 11:11 am

  #667155

  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

  October 20, 2006 at 11:21 am

  #667160

  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

  October 20, 2006 at 12:27 pm

  #667178

  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

  October 20, 2006 at 1:29 pm

  #667191

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