I want to match date and time and compare against utc date
When I write this request
SELECT Convert(datetime,Convert(varchar,CAST(GETUTCDATE() AS DATE))+' '+
CONVERT(varchar, cast(meas_pain.datetime AS time))) FROM meas_pain
it works for me, but when i use the same part in the WHERE clause, it gives the error "Conversion error while converting date and / or time from character string".
SELECT schedules.id
FROM meas_pain LEFT JOIN schedules ON schedules.id=meas_pain.schd_id
WHERE meas_pain.schd_id=9150 AND
Convert(datetime,(Convert(varchar,CAST(GETUTCDATE() AS DATE))+' '+
CONVERT(varchar, cast(meas_pain.datetime AS time)))) <
CONVERT(datetime,DATEADD(Minute,0,getutcdate()))
can someone explain?
source to share
I'm not sure why this error is not showing up in your select statement, as I can reproduce the error using only
SET DATEFORMAT DMY;
SELECT CONVERT(DATETIME, CONVERT(VARCHAR,CAST(GETUTCDATE() AS DATE)))
You are relying on local conversion settings, you must use explicit conversion for example.
SET DATEFORMAT DMY;
SELECT CONVERT(DATETIME, CONVERT(VARCHAR, CAST(GETUTCDATE() AS DATE), 111), 111)
By explicitly defining the date format to convert to both varchar and varchar (111), you can avoid any implied conversions.
However . If your dates / times are stored as such, there should be no need for all conversions to and from varchar, it is more likely that things will go wrong and unnecessary work, you can simply add the time to the date. eg.
DECLARE @Date1 DATETIME = DATEADD(HOUR, 1, GETUTCDATE()),
@Date2 DATETIME = DATEADD(DAY, 1, GETUTCDATE());
SELECT [Date1] = @Date1,
[Date2] = @Date2,
[Date1/Time2] = CAST(CAST(@Date1 AS DATE) AS DATETIME) +
CAST(@Date2 AS TIME);
From what I can gather from your query, you are simply trying to get results where the time is meas_pain.datetime
less than the current UTC time, regardless of the date. Therefore, you should simply simplify your query:
SELECT schedules.id
FROM meas_pain
LEFT JOIN schedules
ON schedules.id = meas_pain.schd_id
WHERE meas_pain.schd_id = 9150
AND CAST(meas_pain.[DateTime] AS TIME) < CAST(GETUTCDATE() AS TIME);
And remove additional redundant transforms.
Simplified example in SQL Fiddle
ADENDUM
Apparently this timing comparison is not what you want (although this is what your request does), so I assume GETUTCDATE () is for demonstration purposes only.
The conversion you are trying to do is equivalent to this:
CAST(CAST(GETUTCDATE() AS DATE) AS DATETIME) + CAST(meas_pain.[DateTime] AS TIME)
source to share