How to cast datetime

Question

0.00/5 (No votes)

See more:

so i have a field with time and a field with date and i want to combine them to get a datetime field

then use this value and compare if it's less than

GETUTCDATE()

but i'm getting an error

What I have tried:

select 
cast(Table.date +' ' +(Select HourValue from table2 where table2.Hour = Table.Hour) +
+ ':' + (Select MinuteValue from table3 where table3.Minute = Table.Minute) as datetime) as Timee

from	Table

Posted 2-Oct-20 4:24am

Member 14800672

Updated 2-Oct-20 5:16am

v3

Add a Solution

Comments

[no name] 2-Oct-20 11:06am

Try selecting into variables first and using those in your "cast" expression. You can at least tell what you're selecting or not.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2020-10-02T05:10:00

Solution 1

datatypes - How to combine date and time to datetime2 in SQL Server? - Database Administrators Stack Exchange[^]

SQL

SELECT 
    DATEADD(day, DATEDIFF(day, '19000101', DatePart), CAST(TimePart AS DATETIME2(7))) As [DateAndTime]
FROM
    YourTable
;

Posted 2-Oct-20 5:10am

Richard Deeming

OriginalGriff · Answer 2 · 2020-10-02T05:16:00

That's probably not going to work, even if you didn't get an error: your "inner selects" are unlikely to return a single value which matches the criteria, even if you could phrase it like that.

Instead, use a JOIN to combine the three tables row-by-row and then work out the details.

But ... that implies that Table.Date is a VARCHAR or NVARCHAR value and if so, is a very bad idea. Change your design to use DATE or DATETIME instead, and then use DATEADD to include your hour and minute values.
And I have my doubts about the whole existence of Table2 and Table3 - they look like you have created them to get round some other design mistake but I can't suggest what that might be because I have no idea what your tables look like or contain!