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

How to convert a xml datetime value into sql datetime? SQL Server

For Example, XML Discussions SQL Server 2005 (1) EnterDateTime (1) Xml (1) I think the

How to convert Excel date seriel number to SQL Datetime? SQL Server

for example, 1 / 1 / 2007 is 39083 in Excel. How to convert the number 39083 to SQL Server datetime type? declare @datetime datetime set @datetime = . . .???. . .(39083) SQL Server Programming Discussions SQL Server (1) Excel (1) Declare (1

Convert DB2 TIME to SQL Server DATETIME SQL Server

Hi, I am trying to import db2 data from an iSeries machine to an SQL Server 2005 instance. On the DB2 I have a column of type TIME. Now I want to convert this column into a datetime value. I have no idea to do this. Thanks a lot SQL Server Integration Services

computer name access sql server SQL Server

Is there a way to determine which workstation is accessing the SQL server? I know in SQL Profile you can see the userid but can you see what actualy workstation is accessing the server? SQL Server Discussions SQL Server 2005 (1) SQL Server (1) Grouping (1) Varchar (1) Bulk

Upsizing large Access table to SQL Server SQL Server

I am using SQL Server 2005 Express and want to upsize large tables from MS Access to SQL Server. The upsizing wizard is skipping the large tables without any error. The QueryTimeout value in

Get Remote SQL Server Datetime SQL Server

Hi All, I have 3 sql servers located at different time zones. Say, CST, PST, EST. Now how can I get current time at EST, PST from the SQL server located at CST? Is there any query to do that? I have a stored proc

What's the go with sql server SQL Server

I was just saying today that sql server is full of silly limitations and I seem to hit on a new one every features with more ways to manipulate things than you can poke a stick at. In sql server we don't even have a full range of date functions (a pretty basic feature

moving from MS Access to SQL Server SQL Server

I am migrating a system from MS Access to SQL Server 2000 and realized that some queries do not support SQL Server like following: Select * from mytable where MyDate = #1 / 1 / 2011# and MyTime = I just wonder

How to extract column information of a table using T-sql SQL Server

Hi folks, Please advise how to extract column information of a table using T-sql. thanks. PeterK SQL Server Programming Discussions SQL Server 2005 (1) SQL Server (1) UNION ALL (1) NOT NULL (1) Grouping (1) Decimal (1

Format in SQL Server SQL Server

column and displays a different result. I canâ€™t find formatting functionalityâ€¦ and why SQL Server help doesnâ€™t cross reference "Format" is confusing to me. What term does SQL use to â€˜change the displayâ€™ of data. Cast and Convert seem to be related Mar 19, 2009â€™ from 3 / 19 / 2009. I want March 19, 2009. Iâ€™m using SQL Server 2000. I also have many string formats to change and could greatly use an