Remove Time from DateTime values returned in Query

Question

With the help of you guys on here, I have the following SQL Query:

SELECT DataLog.TimestampUTC,MeterTags.Name,DataLog.Data
FROM DataLog
INNER JOIN MeterTags
ON DataLog.MeterTagId = MeterTags.MeterTagId
WHERE DataLog.TimeStampUTC between cast(getdate() - 1 as date) and cast(getdate() as date) and
      DataLog.MeterTagId Between 416 AND 462;

This returns a column "TimestampUTC" with YYYY-MM-DD hh:mm:ss. I'd like to drop the time within this column and only display YYYY-MM-DD.

Any help you could give would be really appreciated.

Thanks in advance.

This has already been answered: http://stackoverflow.com/questions/923295/how-can-i-truncate-a-datetime-in-sql-server — Raúl Juárez, Aug 22 '13 at 14:14

score 2 · Accepted Answer · answered Aug 22 '13 at 14:27

SELECT convert(char(10), DataLog.TimestampUTC, 120) as TimestampUTC, 
MeterTags.Name,DataLog.Data
FROM DataLog
INNER JOIN MeterTags
ON DataLog.MeterTagId = MeterTags.MeterTagId
WHERE DataLog.TimeStampUTC between cast(getdate() - 1 as date) and cast(getdate() as date) and
      DataLog.MeterTagId Between 416 AND 462;

score 1 · Answer 2 · answered Aug 22 '13 at 14:10

1

In SQL Server you can convert to Date

select Convert(Date, Convert(datetime, '2013/01/01 12:53:45'))

results:

2013-01-01

answered Aug 22 '13 at 14:10

gariel

687
3
13

score 0 · Answer 3 · edited Aug 22 '13 at 15:01

0

You should use : CAST(DataLog.TimestampUTC as date)

The code looks like:

SELECT CAST(DataLog.TimestampUTC as date),MeterTags.Name,DataLog.Data
FROM DataLog
INNER JOIN MeterTags
ON DataLog.MeterTagId = MeterTags.MeterTagId
WHERE DataLog.TimeStampUTC between cast(getdate() - 1 as date) and cast(getdate() as date) and
      DataLog.MeterTagId Between 416 AND 462;

edited Aug 22 '13 at 15:01

Nbr44

2,042
13
15

answered Aug 22 '13 at 14:43

Justas

132
4

Remove Time from DateTime values returned in Query

3 Answers3