CODEWITHSUNDEEP
sqlsql-serversql-server-2008asp-classic
Trinitrotoluene
Trinitrotoluene

Reputation: 1458

MSSQL / ASP Date Format UK Datetime issue

This is quite complicated to explain so I apologise in advance. I am in the UK so this is in relation to datetime format for the UK.

I have a VBS that is submitting to an ASP page which then inserts into an SQL Database with a datetime field.

The problem is that my format in MSSQL should be YYYY-MM-DD HH-MM-SS.000 - it has inserted above as YYYY-DD-MM HH-MM-SS.000

This is causing huge problems when I am processing these dates because it thinks the date is in the future. Interestingly it only seems to occur on certain dates (I suspect this happens when the day number is higher than 12 and it knows the month can't be 13 so formats it properly.

How can I set this permanently to avoid SQL inserting the dates the wrong way round? Any help would be appreciated.

Upvotes: 0

Views: 725

Answers (2)

gpinkas
gpinkas

Reputation: 2591

You can always use the ISO format in SQL Server statements. I have written a function for this purpose.

function getIsoTimestamp(dt_in, s_time_zone, n_options)
        ' time zone format examples: "" = local time; "Z" = Zulu time/UTC, "+0100" = MEZ/CET
        ' options:
        '   1: remove time information
        '   2: remove date delimiter char
        '   4: remove time delimiter char
        dim s_out, s_year, s_month, s_day, s_hour, s_min, s_sec
        dim s_date_delim, s_time_delim

        s_out = ""

        if isDate(dt_in) then
                s_date_delim = iff((n_options AND 2)=2, "", "-")
                s_time_delim = iff((n_options AND 4)=4, "", ":")

                ' format date
                s_year = year(dt_in)
                s_month = Right(100 + month(dt_in), 2)
                s_day = Right(100 + day(dt_in), 2)
                s_out = s_year & s_date_delim & s_month & s_date_delim & s_day

                ' format time
                if (n_options AND 1)=0 then
                        s_hour = Right(100 + hour(dt_in), 2)
                        s_min = Right(100 + minute(dt_in), 2)
                        s_sec = Right(100 + second(dt_in), 2)
                        s_out = s_out & "T" & s_hour & s_time_delim & s_min & s_time_delim & s_sec & s_time_zone
                end if
        end if

        getIsoTimestamp = s_out
end function

You can use getIsoTimestamp(now(), "", 0) to test it.

Upvotes: 0

Ravi Singh
Ravi Singh

Reputation: 2080

You could use SET DATEFORMAT, like in this example

declare @dates table (orig varchar(50) ,parsed datetime)

SET DATEFORMAT ydm;

insert into @dates
select '2008-09-01','2008-09-01'

SET DATEFORMAT ymd;
insert into @dates
select '2008-09-01','2008-09-01'

select * from @dates

Reference

Upvotes: 1

Related Questions