Datetime (Want just Date) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Datetime (Want just Date)

Author

Topic

benking9987
Posting Yak Master

124 Posts

Posted - 2009-11-06 : 14:10:43

I have a T-SQL statement that appends a TimeCard table that I'm scheduling with a job...

INSERT INTO TimeCard ( SSN, Date )
SELECT Employee.SSN, GetDate() AS Expr1
FROM Employee;

I need this to pass through just the date, instead of the Date and Time. Do I need to change the datatype on the destination table, or is there something I can add to the T-SQL to only give me the date, without time?

Thanks in advance.

benking9987
Posting Yak Master

124 Posts

Posted - 2009-11-06 : 14:12:33

Actually, I need it to return the date and time, but in this format (regardless of the ACTUAL time when the SQL is run)

2009-11-05 00:00:00.000

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-11-06 : 14:23:07

select DATEADD(day,DATEDIFF(day,0,getdate()),0)
Jim

Everyday I learn something that somebody else already knew

benking9987
Posting Yak Master

124 Posts

Posted - 2009-11-06 : 17:34:25

Perfect. This is exactly what I need. Thank you.

Out of curiosity, how do you specify a particular time. For example, if I have a Job that runs every day at 11:00 pm and I want to update a particular record to the same day at 12:00 noon.

Example: Its 11/06/2009 11:00 pm. I want to update a record with '11/06/2009 12:00 pm'

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-11-06 : 19:41:34

Look up DATEADD in books online.

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-06 : 22:20:46

DATEADD(HH,DATEADD(day,DATEDIFF(day,0,getdate()),0),12)

????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-09 : 01:52:04

quote:
Originally posted by X002548

DATEADD(HH,DATEADD(day,DATEDIFF(day,0,getdate()),0),12)

????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

That should be

select DATEADD(HH,12,DATEADD(day,DATEDIFF(day,0,getdate()),0))

or

select DATEADD(day,DATEDIFF(day,0,getdate()),'12:00')

Madhivanan

Failing to plan is Planning to fail

benking9987
Posting Yak Master

124 Posts

Posted - 2009-11-09 : 09:45:42

Thank you. This is exactly what I needed.

benking9987
Posting Yak Master

124 Posts

Posted - 2009-11-09 : 11:05:00

Since this has been a really helpful post, I thought I might indulge myself and ask a related question.

If I have a tuple with a value in a datetime column and I want to update another datetime column based on what is input on the first datetime column, what would be the syntax for that?

Example: column 1 says 1:15 pm and I want to add 1 hour to that and populate column 2 with 2:15 pm...

Thanks in advance for any help rendered.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-09 : 11:16:58

just use dateadd(hh,1,col1) as value for col2

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-09 : 11:30:04

quote:
Originally posted by visakh16

just use dateadd(hh,1,col1) as value for col2

HUH?

SELECT dateadd(hh,1,GetDate())

It was a driveby

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-09 : 11:36:53

quote:
Originally posted by X002548

quote:
Originally posted by visakh16

just use dateadd(hh,1,col1) as value for col2

HUH?

SELECT dateadd(hh,1,GetDate())

It was a driveby

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

i was answering to this
If I have a tuple with a value in a datetime column and I want to update another datetime column based on what is input on the first datetime column, what would be the syntax for that?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources