Datetime

Question

Post reply

Datetime

myschif

Say Hey Kid

Points: 695
More actions
June 26, 2008 at 3:59 pm

#189856

How can I set the date to the current date in the following format:
2008-06-26 00:00:00:000
conversely, how do I get the current time in the following format:
1900-01-01 02:45:57:000
Thank you for any assistance

"Some like to understand what they believe in. Others like to believe in what they understand."
--Stanislaus J. Lec

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 1

SELECT DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0),

GETDATE()-DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Hari.Sharma SSCertifiable Points: 6904 More actions · Answer 2

--Try this code

DECLARE @DATE DATETIME

SET @DATE = GETDATE()

--1. Set the date to the current date in format:- 2008-06-26 00:00:00:000

SELECT CAST(CONVERT(VARCHAR(20),@DATE,102) AS DATETIME)

--2. Get the current time in the following format:2008-06-27 14:37:59.133

SELECT @Date

/*

You can use STYLE from 100 to 114 to convert datetime or smalldatetime data to character data

*/

Cheers,
Hari
Tips & Tricks for SQL BI Developers

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

Careful... using CONVERT to strip the time off the date (1st example) or the date off the time (2nd example) can slow queries down quite a bit.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)