Dateformat
-
SET DATEFORMAT mdy
GO
DECLARE @datevar datetime
SET @datevar = '12/31/98'
SELECT @datevar
GO
Returns 1998-12-31 00:00:00.000 instead of 12-31-1998 00:00:00.000
Why is this behaviour?
-
Check this out (from the Books online) :
SET DATEFORMAT mdy
GO
DECLARE @datevar datetime
SET @datevar = '12/31/98'
SELECT @datevar
GO
SET DATEFORMAT ydm
GO
DECLARE @datevar datetime
SET @datevar = '98/31/12'
SELECT @datevar
GO
SET DATEFORMAT ymd
GO
DECLARE @datevar datetime
SET @datevar = '98/12/31'
SELECT @datevar
GO
DATEFORMAT affects the way the input is read, if you want to change the ouput, you must convert to varchar and use one of the styles listed in the books online.
-
On a note on formats for input into datetime datatypes
Don't use anything else than yyyymmdd as input format for dates. Do your utmost to enforce this, and your dating troubles will be minimal (not affecting dating girls, though)
The format used for storing a date ( ie 20050902 ) has absolutely nothing to do with how the date looks when you want to display it again!
Why would you bother then? Well, yyyymmdd is the only format that is unambigous and language independent - it will never ever be 'misunderstood' or converted or rejected by the server (as long as within the datetime range of course). You can be absolutely positively sure that the date you store is indeed the same date that will be read later on. You don't have to worry about dateformat settings or language settings on the server, the client or any tier in between!
For the interested (should be everyone
) these are interesting reads on dates and SQL Server:'The ultimate guide to the datetime datatypes '
http://www.karaszi.com/SQLServer/info_datetime.asp
'How do I delimit/format dates for database entry?'
http://www.aspfaq.com/show.asp?id=2023
/Kenneth
-
Nobody really answered your question, so here goes...refer to the BOL for CONVERT.
Run this:
SELECT GETDATE(),
CONVERT(VARCHAR(20), GETDATE(), 101)
The BOL, under CONVERT shows the different STYLE codes (that's what the 101 is).
SET DATEFORMAT is for inputting dates
CONVERT is for retrieving dates
-SQLBill
-
"DATEFORMAT affects the way the input is read, if you want to change the ouput, you must convert to varchar and use one of the styles listed in the books online."
He's better off learning where to get the info...
-
I am trying the same in this way which fails.
SELECT GETDATE(),
CONVERT(datetime, GETDATE(), 101)
Thanks All
Jagan
-
You say it failed, but not how....
I'm guessing it's because you got slants instead of hyphens....
Use style 110......
-SQLBill
-
We have to use VARCHAR instead of Datetime.
SELECT GETDATE(),
CONVERT(datetime, GETDATE(), 101)
This has no effect on output
SELECT GETDATE(),
CONVERT(VARCHAR(20), GETDATE(), 101)
Giving the exact output.
-
If you don't change to varchar, you keep the presentation format of the datetime.
-
Can You explain what is presentation format ?
Jagan
-
Books online > Convert, there's a huge section about the datetime in there. You need to learn to find this stuff on your own. We can't show you every little details about sql, it's just too big.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply