Database

Rocky# wrote:

whenever u user datetime in SQL Server it always appends a default time

Select Getdate()

Pete O'Hanlon wrote:

Guess what. Created doesn't get a default value. What you do get is an error:

Insert into Test(text, created)
values ('my test', CAST('7/23/2008' as  datetime))

select substring(convert(varchar, convert(datetime, cast(DayDate AS char(8))),20),1,10) AS Date<br />
from TableName where DayDate = 20080721

CREATE FUNCTION dbo.FormatDateTime 
( 
    @dt DATETIME, 
    @format VARCHAR(16) 
) 
RETURNS VARCHAR(64) 
AS 
BEGIN 
    DECLARE @dtVC VARCHAR(64) 
    SELECT @dtVC = CASE @format 
 
    WHEN 'LONGDATE' THEN 
 
        DATENAME(dw, @dt) 
        + ',' + SPACE(1) + DATENAME(m, @dt) 
        + SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2)) 
        + ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4)) 
 
    WHEN 'LONGDATEANDTIME' THEN 
 
        DATENAME(dw, @dt) 
        + ',' + SPACE(1) + DATENAME(m, @dt) 
        + SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2)) 
        + ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4)) 
        + SPACE(1) + RIGHT(CONVERT(CHAR(20), 
        @dt - CONVERT(DATETIME, CONVERT(CHAR(8), 
        @dt, 112)), 22), 11) 
 
    WHEN 'SHORTDATE' THEN 
 
        LEFT(CONVERT(CHAR(19), @dt, 0), 11) 
 
    WHEN 'SHORTDATEANDTIME' THEN 
 
        REPLACE(REPLACE(CONVERT(CHAR(19), @dt, 0), 
            'AM', ' AM'), 'PM', ' PM') 
 
    WHEN 'UNIXTIMESTAMP' THEN 
 
        CAST(DATEDIFF(SECOND, '19700101', @dt) 
        AS VARCHAR(64)) 
 
    WHEN 'YYYYMMDD' THEN 
 
        CONVERT(CHAR(8), @dt, 112) 
 
    WHEN 'YYYY-MM-DD' THEN 
 
        CONVERT(CHAR(10), @dt, 23) 
 
    WHEN 'YYMMDD' THEN 
 
        CONVERT(VARCHAR(8), @dt, 12) 
 
    WHEN 'YY-MM-DD' THEN 
 
        STUFF(STUFF(CONVERT(VARCHAR(8), @dt, 12), 
        5, 0, '-'), 3, 0, '-') 
 
    WHEN 'MMDDYY' THEN 
 
        REPLACE(CONVERT(CHAR(8), @dt, 10), '-', SPACE(0)) 
 
    WHEN 'MM-DD-YY' THEN 
 
        CONVERT(CHAR(8), @dt, 10) 
 
    WHEN 'MM/DD/YY' THEN 
 
        CONVERT(CHAR(8), @dt, 1) 
 
    WHEN 'MM/DD/YYYY' THEN 
 
        CONVERT(CHAR(10), @dt, 101) 
 
    WHEN 'DDMMYY' THEN 
 
        REPLACE(CONVERT(CHAR(8), @dt, 3), '/', SPACE(0)) 
 
    WHEN 'DD-MM-YY' THEN 
 
        REPLACE(CONVERT(CHAR(8), @dt, 3), '/', '-') 
 
    WHEN 'DD/MM/YY' THEN 
 
        CONVERT(CHAR(8), @dt, 3) 
 
    WHEN 'DD/MM/YYYY' THEN 
 
        CONVERT(CHAR(10), @dt, 103) 
 
    WHEN 'HH:MM:SS 24' THEN 
 
        CONVERT(CHAR(8), @dt, 8) 
 
    WHEN 'HH:MM 24' THEN 
 
        LEFT(CONVERT(VARCHAR(8), @dt, 8), 5) 
 
    WHEN 'HH:MM:SS 12' THEN 
 
        LTRIM(RIGHT(CONVERT(VARCHAR(20), @dt, 22), 11)) 
 
    WHEN 'HH:MM 12' THEN 
 
        LTRIM(SUBSTRING(CONVERT( 
        VARCHAR(20), @dt, 22), 10, 5) 
        + RIGHT(CONVERT(VARCHAR(20), @dt, 22), 3)) 
 
    ELSE 
 
        'Invalid format specified' 
 
    END 
    RETURN @dtVC 
END 
GO

select dbo.FormatDateTime(convert(datetime, cast(DayDate AS char(8))),'YYYY-MM-DD') AS Date
from TableName where DayDate = 20080721

DECLARE @now DATETIME 
SET @now = GETDATE() 
 
PRINT dbo.FormatDateTime(@now, 'LONGDATE') 
PRINT dbo.FormatDateTime(@now, 'LONGDATEANDTIME') 
PRINT dbo.FormatDateTime(@now, 'SHORTDATE') 
PRINT dbo.FormatDateTime(@now, 'SHORTDATEANDTIME') 
PRINT dbo.FormatDateTime(@now, 'UNIXTIMESTAMP') 
PRINT dbo.FormatDateTime(@now, 'YYYYMMDD') 
PRINT dbo.FormatDateTime(@now, 'YYYY-MM-DD') 
PRINT dbo.FormatDateTime(@now, 'YYMMDD') 
PRINT dbo.FormatDateTime(@now, 'YY-MM-DD') 
PRINT dbo.FormatDateTime(@now, 'MMDDYY') 
PRINT dbo.FormatDateTime(@now, 'MM-DD-YY') 
PRINT dbo.FormatDateTime(@now, 'MM/DD/YY') 
PRINT dbo.FormatDateTime(@now, 'MM/DD/YYYY') 
PRINT dbo.FormatDateTime(@now, 'DDMMYY') 
PRINT dbo.FormatDateTime(@now, 'DD-MM-YY') 
PRINT dbo.FormatDateTime(@now, 'DD/MM/YY') 
PRINT dbo.FormatDateTime(@now, 'DD/MM/YYYY') 
PRINT dbo.FormatDateTime(@now, 'HH:MM:SS 24') 
PRINT dbo.FormatDateTime(@now, 'HH:MM 24') 
PRINT dbo.FormatDateTime(@now, 'HH:MM:SS 12') 
PRINT dbo.FormatDateTime(@now, 'HH:MM 12') 
PRINT dbo.FormatDateTime(@now, 'goofy')

DECLARE @d DECIMAL(15,6) ;SET @d = 20080721.150825 

SELECT 
  @d AS [as DECIMAL]
, CAST( LEFT(@d ,8) AS SMALLDATETIME ) AS [as SMALLDATETIME]
, CAST( LEFT(@d ,8) AS DATETIME      ) AS [as DATETIME]
, CONVERT( NVARCHAR(10),   CAST( LEFT(@d ,8) AS DATETIME )  , 121 )    AS [as NVARCHAR(10)]

SELECT CONVERT(VARCHAR(20),CONVERT(DATETIME,'20080819'),106)<br />
SELECT CONVERT(VARCHAR(20),CONVERT(DATETIME,'20080819'),103)<br />
SELECT CONVERT(VARCHAR(20),CONVERT(DATETIME,'20080819'),110)<br />
SELECT CONVERT(VARCHAR(20),CONVERT(DATETIME,'20080819'),111)<br />