• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • 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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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