MySQL - how to declare date in a table in sql 2005

Asked By Ajay Chhadwa on 26-Jul-11 01:09 AM
i m using sql 2005,
how to declare a date in ddmmyyy format in a table
i m using datetime datatype but it is acepting date in yyyymmdd format and also time
i want that the table must accept only date not time
Ravi S replied to Ajay Chhadwa on 26-Jul-11 01:14 AM
HI

try this

--declare our date variables
DECLARE @LowerBoundDate DATETIME
DECLARE @UpperBoundDate DATETIME
DECLARE @IteratingDate DATETIME

--set the initial dates
SET @LowerBoundDate = '1/1/1980'
SET @UpperBoundDate = '1/1/2040'

--create our temporary table
DECLARE @TempDateDimension TABLE
(
    Date DATETIME
  ,  [Year] INT
  ,  [Month] INT
  ,  [Day]  INT
)

--set the iterating date to the lowerbound date
SET @IteratingDate = @LowerBoundDate

--continue to loop until our iterating date is the same as the target date
WHILE @IteratingDate <= @UpperBoundDate
BEGIN
  --insert the date to our temporary table
  INSERT @TempDateDimension
  (
      Date
    ,  [Year]
    ,  [Month]
    ,  [Day]
  )
  VALUES
  (  
      @IteratingDate
    ,  Year(@IteratingDate)
    ,  Month(@IteratingDate)
    ,  Day(@IteratingDate)
  )
  --increase the value of our iterating date
  SET @IteratingDate = @IteratingDate + 1
END


refer the link also
http://devpinoy.org/blogs/keithrull/archive/2007/05/11/how-to-create-date-dimension-tables-in-sql-server-2000-2005.aspx
Ravi S replied to Ajay Chhadwa on 26-Jul-11 01:17 AM
HI

Execute the following Microsoft SQL Server T-SQL datetime and date formatting scripts in Management Studio Query Editor to demonstrate the multitude of temporal data formats available in SQL Server.

First we start with the conversion options available for sql datetime formats with century (YYYY or CCYY format). Subtracting 100 from the Style (format) number will transform dates without century (YY). For example Style 103 is with century, Style 3 is without century. The default Style values – Style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121 – always return the century (yyyy) format.

 

– Microsoft SQL Server T-SQL date and datetime formats

– Date time formats – mssql datetime 

– MSSQL getdate returns current system date and time in standard internal format

SELECT convert(varchar, getdate(), 100) – mon dd yyyy hh:mmAM (or PM)

                      – Oct  2 2008 11:01AM      

SELECT convert(varchar, getdate(), 101) – mm/dd/yyyy 10/02/2008          

SELECT convert(varchar, getdate(), 102) – yyyy.mm.dd – 2008.10.02       

SELECT convert(varchar, getdate(), 103) – dd/mm/yyyy

SELECT convert(varchar, getdate(), 104) – dd.mm.yyyy

SELECT convert(varchar, getdate(), 105) – dd-mm-yyyy

SELECT convert(varchar, getdate(), 106) – dd mon yyyy

SELECT convert(varchar, getdate(), 107) – mon dd, yyyy

SELECT convert(varchar, getdate(), 108) – hh:mm:ss

SELECT convert(varchar, getdate(), 109) – mon dd yyyy hh:mm:ss:mmmAM (or PM)

                      – Oct  2 2008 11:02:44:013AM   

SELECT convert(varchar, getdate(), 110) – mm-dd-yyyy

SELECT convert(varchar, getdate(), 111) – yyyy/mm/dd

SELECT convert(varchar, getdate(), 112) – yyyymmdd

SELECT convert(varchar, getdate(), 113) – dd mon yyyy hh:mm:ss:mmm

                    – 02 Oct 2008 11:02:07:577   

SELECT convert(varchar, getdate(), 114) – hh:mm:ss:mmm(24h)

SELECT convert(varchar, getdate(), 120) – yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar, getdate(), 121) – yyyy-mm-dd hh:mm:ss.mmm

SELECT convert(varchar, getdate(), 126) – yyyy-mm-ddThh:mm:ss.mmm

                    – 2008-10-02T10:52:47.513

– SQL create different date styles with t-sql string functions

SELECT replace(convert(varchar, getdate(), 111), ‘/’, ‘ ‘) – yyyy mm dd

SELECT convert(varchar(7), getdate(), 126)         – yyyy-mm

SELECT right(convert(varchar, getdate(), 106), 8)      – mon yyyy

————

– SQL Server date formatting function – convert datetime to string

————

– SQL datetime functions

– SQL Server date formats

– T-SQL convert dates

– Formatting dates sql server

CREATE FUNCTION dbo.fnFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32))

RETURNS VARCHAR(32)

AS

BEGIN

  DECLARE @StringDate VARCHAR(32)

  SET @StringDate = @FormatMask

  IF (CHARINDEX (‘YYYY’,@StringDate) > 0)

     SET @StringDate = REPLACE(@StringDate, ‘YYYY’,

             DATENAME(YY, @Datetime))

  IF (CHARINDEX (‘YY’,@StringDate) > 0)

     SET @StringDate = REPLACE(@StringDate, ‘YY’,

             RIGHT(DATENAME(YY, @Datetime),2))

  IF (CHARINDEX (‘Month’,@StringDate) > 0)

     SET @StringDate = REPLACE(@StringDate, ‘Month’,

             DATENAME(MM, @Datetime))

  IF (CHARINDEX (‘MON’,@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)

     SET @StringDate = REPLACE(@StringDate, ‘MON’,

             LEFT(UPPER(DATENAME(MM, @Datetime)),3))

  IF (CHARINDEX (‘Mon’,@StringDate) > 0)

     SET @StringDate = REPLACE(@StringDate, ‘Mon’,

                     LEFT(DATENAME(MM, @Datetime),3))

  IF (CHARINDEX (‘MM’,@StringDate) > 0)

     SET @StringDate = REPLACE(@StringDate, ‘MM’,

          RIGHT(’0′+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))

  IF (CHARINDEX (‘M’,@StringDate) > 0)

     SET @StringDate = REPLACE(@StringDate, ‘M’,

               CONVERT(VARCHAR,DATEPART(MM, @Datetime)))

  IF (CHARINDEX (‘DD’,@StringDate) > 0)

     SET @StringDate = REPLACE(@StringDate, ‘DD’,

             RIGHT(’0′+DATENAME(DD, @Datetime),2))

  IF (CHARINDEX (‘D’,@StringDate) > 0)

     SET @StringDate = REPLACE(@StringDate, ‘D’,

                     DATENAME(DD, @Datetime))   

RETURN @StringDate

END

GO

 

– Microsoft SQL Server date format function test

– MSSQL formatting dates

SELECT dbo.fnFormatDate (getdate(), ‘MM/DD/YYYY’)       – 01/03/2012

SELECT dbo.fnFormatDate (getdate(), ‘DD/MM/YYYY’)       – 03/01/2012

SELECT dbo.fnFormatDate (getdate(), ‘M/DD/YYYY’)      – 1/03/2012

SELECT dbo.fnFormatDate (getdate(), ‘M/D/YYYY’)       – 1/3/2012

SELECT dbo.fnFormatDate (getdate(), ‘M/D/YY’)         – 1/3/12

SELECT dbo.fnFormatDate (getdate(), ‘MM/DD/YY’)       – 01/03/12

SELECT dbo.fnFormatDate (getdate(), ‘MON DD, YYYY’)     – JAN 03, 2012

SELECT dbo.fnFormatDate (getdate(), ‘Mon DD, YYYY’)     – Jan 03, 2012

SELECT dbo.fnFormatDate (getdate(), ‘Month DD, YYYY’)     – January 03, 2012

SELECT dbo.fnFormatDate (getdate(), ‘YYYY/MM/DD’)       – 2012/01/03

SELECT dbo.fnFormatDate (getdate(), ‘YYYYMMDD’)       – 20120103

SELECT dbo.fnFormatDate (getdate(), ‘YYYY-MM-DD’)       – 2012-01-03

– CURRENT_TIMESTAMP returns current system date and time in standard internal format

SELECT dbo.fnFormatDate (CURRENT_TIMESTAMP,‘YY.MM.DD’)    – 12.01.03

GO

————

refer the link also

http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/

Kalit Sikka replied to Ajay Chhadwa on 26-Jul-11 01:24 AM
use this:

SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '')

Please refer this link:

http://www.sql-server-helper.com/tips/date-formats.aspx
Reena Jain replied to Ajay Chhadwa on 26-Jul-11 01:39 AM
Hi,

You can use

SET DATEFORMAT dmy;
eg
-- Set date format to day/month/year.
SET DATEFORMAT dmy;
GO
DECLARE @datevar datetime2 = '31/12/2008 09:01:01.1234567';
SELECT @datevar;
GO
-- Result: 2008-12-31 09:01:01.123
SET DATEFORMAT dmy;
GO
DECLARE @datevar datetime2 = '12/31/2008 09:01:01.1234567';
SELECT @datevar;
GO
-- Result: Msg 241: Conversion failed when converting date and/or time -- from character string.
 
GO

Or, you can use CONVERT in the select statement. Date formats are usually better controlled in the client
.

Hope this will help you