getdate function

Question

getdate function

Nali

SSCommitted

Points: 1525
More actions
September 25, 2007 at 3:32 pm

#76785

How do I generate a date stamp that will look something like this when I do a SELECT on 9/25/2007 at 5:44 PM?
925071744
Thanks...Nali

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

make sure you use a field that is datetime. it can contain both the date and the time together; if you stored it as a varchar, change it to solve problems in the future.

here's an example:

declare @date datetime

set @date=getdate()

select convert(varchar,@date,101) + ' at ' + convert(varchar,@date,108)

results: '09/26/2007 at 17:43:06'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Nali SSCommitted Points: 1525 More actions · Answer 2

Thanks, but I was trying to make it look like: 925071744

Lowell SSC Guru Points: 323518 More actions · Answer 3

i provided an example when using the convert function; take a look at books on line...

most people suggest using a datestamp like 200709261727 instead of the format you were suggesting;

for example without a preceeding zero in your format, how would you know 121071727 is 12/1/07 ir 1/21/07? or how would you know it's not european style formatting of d/m/year?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

You are aware that 92520071744 isn't a valid datetime value?

Also, if the day was for example the fifth of september....

Would it then be 9520071744 or 90520071744 ?

And the same thing goes for the timepart.

Leading zeroes or not?

If leading is desired, then wouldnt september be represented as 09 instead of 9 as in teh example?

/Kenneth

Bob Fazio SSChampion Points: 10794 More actions · Answer 5

USE [master]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:Bob Fazio

-- Create date: 06/21/2007

-- Description:Function Returns a Date in the form of YYYYMMDDHHMISS

-- =============================================

alter FUNCTION [to_yyyymmddhhmiss]

(

-- Date Time to work with

@theDate datetime

)

RETURNS VARCHAR(14)

AS

BEGIN

-- Declare the return variable here

DECLARE @Result varchar(14)

-- Add the T-SQL statements to compute the return value here

SELECT @Result =

DATEPART(yyyy,@theDate) * 10000000000

+ DATEPART(mm,@theDate) * 100000000

+ DATEPART(dd,@theDate) * 1000000

+ DATEPART(hh,@theDate) * 10000

+ DATEPART(mi,@theDate) * 100

+ DATEPART(ss,@theDate)

-- Return the result of the function

RETURN @Result

END