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: 255
  • Last Modified:

SQL Dates

I've got a date field
[DateTimeEntered] [datetime] NOT NULL, Output 2012-09-19 04:17:55.937

i want to say if DateTimeEntered < getdate

I've tried but its not quite correct.

WHERE [Mf] = 'YES' AND [DateTimeEntered] < CONVERT(NVARCHAR(50), GETDATE(), 103)
0
aneilg
Asked:
aneilg
  • 8
  • 5
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will surely want to read this article:
http://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html

so, you want everything until yesterday included:
WHERE [Mf] = 'YES' AND [DateTimeEntered] < CONVERT(datetime, CONVERT(VARCHAR(10), GETDATE(), 120), 120) 

Open in new window


the VARCHAR(10) must be kept as is, while the 2x 120 could be 2x 103 ,but it wouldn't matter as long as the format will have 10 characters in length.
0
 
aneilgAuthor Commented:
i want to strip '2012-09-19 04:17:55.937' to '2012-09-19'

so basically say where date entered '2012-09-18'  <  GETDATE().

The problem with '2012-09-19 04:17:55.937'  is the minutes.
0
 
aneilgAuthor Commented:
another question is how do i do something like.

DECLARE @DateTimeEntered DATE = (GETDATE(),103)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LIONKINGCommented:
To strip the time you can use this:

DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)

And for your column it would be

DATEADD(DAY,DATEDIFF(DAY,0,[DateTimeEntered]),0)
0
 
LIONKINGCommented:
another question is how do i do something like.

DECLARE @DateTimeEntered DATE = (GETDATE(),103)
What exactly do you want to achieve with this?
0
 
aneilgAuthor Commented:
thanks for all your help.

the first part is fine thanks.

the other question is i am loading the date in, but its in the following format 2012-09-19 04:17:55.937.

but i want the format to be 2012-09-19 00:00:00.000.

i've tried
DECLARE @DateTimeEntered DATE= (YEAR(GETDATE()) * 10000) + (MONTH(GETDATE()) * 100) + DAY(GETDATE())

but get Operand type clash: int is incompatible with date
0
 
LIONKINGCommented:
Did you try what I posted?
0
 
aneilgAuthor Commented:
yeas thanks for that, that worked its the other question now.
sorry for the confusion.
0
 
LIONKINGCommented:
Try

DECLARE @DateTimeEntered DATETIME = DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
did you actually try my original suggestion?
0
 
aneilgAuthor Commented:
hi angelll that worked fine thanks, its the other one i am looking at.

thanks.
0
 
aneilgAuthor Commented:
if i try
DECLARE @DateTimeEntered DATETIME = DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)
PRINT @DateTimeEntered

i get Sep 19 2012 12:00AM
but i need 2012-09-19 00:00:00.000
0
 
LIONKINGCommented:
Try this

DECLARE @DateTimeEntered DATETIME = DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)
SELECT @DateTimeEntered
0
 
aneilgAuthor Commented:
perfect thanks guys, really appreciate all the help you guys give.
0
 
aneilgAuthor Commented:
thanks
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 8
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now