Date time problem.

[funcrecall12]

I have this date in my Table with this format
"2003-07-07 17:22:00"

how can i get the date or convert the date on this format
"07/07/04" or "07/07/2004"

Please help.

Thanks

JERRY

 

[r937]

look up CONVERT in Books OnLine

you want style 1 or 101

either that, or style 3 or 103

see, it's kinda hard to tell from 07/07 which is the month and which is the day

by the way, dates are not stored in sql server in that format -- dates are stored as two integers



rudy

http://r937.com/

SQL Consulting

 

[obislavu]

The date that you see in the table using SQL Analyzer is not the format that is stored in the databse. A datetime is stored as a long and what you see is a default format used by SQL Analyzer.
On the other hand, if you right click on the table and select Open Table ->Return All Rows , you will see the columns that are DateTime type displayed in a format that is depending on the Regional Settings.
If you need in applications (API) , stored procedures, scripts, triggers to manipulate DateTime fields you should use CONVERT whith a format that is recognized by all languages like here:
SELECT CIAComments
FROM dbo._tComments
WHERE RecDate = CONVERT(datetime, '02/29/2004', 101)
Here the 101 is the style to be used when converting.
-obislavu-

 

[funcrecall12]

Oops sorry for that !!

I have this date in my Table with this format
this is the format is
yyyy-mm-dd 00:00:00
"2003-07-07 17:22:00" iwill change it to.

"2003-07-15 17:22:00"



how can i get the date or convert the date on this format
"07/07/04" or "07/07/2004" or "07/15/2004" or "07/15/04"


Please help.

Thanks

JERRY

 

[r937]

jerry, have a look at CONVERT in Books OnLine

rudy

http://r937.com/

SQL Consulting

 

[dbomrrsm]

Below function will get date you want play around with the order of the date parts to get what you want.

CREATE FUNCTION justdate (@dttm DATETIME)
RETURNS nvarchar(10)
AS


BEGIN
DECLARE @udfddmmyyyy nvarchar(10)
SELECT @udfddmmyyyy = RIGHT('0' + CAST(Day(@dttm) AS
Varchar(2)), 2) + '/' + RIGHT('0' + CAST(Month(@dttm) AS
Varchar(2)), 2) + '/' + CAST(Year(@dttm) AS Varchar(4))
RETURN(@udfddmmyyyy)
END

Example
--SELECT dbo.justdate(getdate())

 

[Mayoor]

I have a similar problem and its confusing the hell out of me.

I have a field in my database called datfinished which has values in the format

dd/mm/yyyy hh:mm:ss

I need to convert this to the same format as getdate(). Can someone advise what the default format of getdate() is and how i might convert my datefinished field into this format so I can compare between the 2.

Thanks for any help in advace

Mayoor

 

[dbomrrsm]

Mayoor

the default for getdate() is yyyy-mm-dd hh:mm etc

if you want to compare just the date from your datfinished field with Getdate() run the below UD_function on both getdate() and your datfinished field and they will both be output in the same format.

If you want to compare the exact time aswell as the date you will need to expand the function to include lines that also show the time.


CREATE FUNCTION justdate (@dttm DATETIME)
RETURNS nvarchar(10)
AS


BEGIN
DECLARE @udfddmmyyyy nvarchar(10)
SELECT @udfddmmyyyy = RIGHT('0' + CAST(Day(@dttm) AS
Varchar(2)), 2) + '/' + RIGHT('0' + CAST(Month(@dttm) AS
Varchar(2)), 2) + '/' + CAST(Year(@dttm) AS Varchar(4))
RETURN(@udfddmmyyyy)
END

--Example
--SELECT dbo.justdate(getdate())
--SELECT dbo.justdate(datfinished)

hope this helps