Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2147
  • Last Modified:

Need to convert and combine date and time on SQL

Hello,
I have date and time entered separately into my database table. Date is of the data type datetime so it is in the format of 2007-04-10 00:00:00.000
Time is enetered as a varchar(7) - so it is in the format of: 06:00PM
I am doing conversion of time entries using the following syntax: CONVERT(datetime, sl.bgn_time) that produces: 1900-01-01 18:00:00.000
I need to combine date and time entries into one datetime format so I can compare that to getdate() and select all entries that have datediff(hh,datetime,getdate())<24 - another words -   need to get all entries that have date and time set at les then 24 hours from now.
I need to do it in the stored procedure so it all has to be in T-SQL.
Thanks for your help.
0
lescluster
Asked:
lescluster
1 Solution
 
chiragkhabariaCommented:
datediff(hh,DateAdd(hh,datepart(hh,Convert(DateTime,TimeCol)),DateCol) ,getdate())<24
0
 
GokulmCommented:
Try this to concatenate date and time

SELECT CONVERT(datetime, datefield, 101) + CONVERT(datetime, sl.bgn_time)
0
 
ksaulCommented:
Does the date field ever have a time component?  If not (or if it is always 00:00:00.000,  you should be able to add them simply as

DateField + TimeField

SELECT *
FROM Table
WHERE (DateField + Timefield) < DateAdd(day, -1, getdate())

If you need to remove the time from the date field:

SELECT *
FROM Table
WHERE (convert(datetime,convert(varchar(11), DateField, 120) + ' ' + TimeField)) < DateAdd(day, -1, getdate())
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now