convert an Access date formated fiel to an SQL dateformat (1 Viewer)

eatraas

Registered User.
Local time
Today, 07:44
Joined
Jan 23, 2009
Messages
96
Hi,

i"m having trouble to convert a date field from Access to SQL.
What is the field type i need in SQL (I've tried the threee formats)?

Thanks in advance


E.Traas
 

Isaac

Lifelong Learner
Local time
Today, 07:44
Joined
Mar 14, 2017
Messages
8,738
There are 20 possible answers to this, but I believe the one most likely to survive regions, regional settings and database settings is:

'YYYY-MM-DD'

...That's what I always use.

If you're talking about what column datatype you need, then I would recommend DateTime, unless you have some extremely unusual time capture needs. DateTime will store the time element - but it's always better to capture that up front and strip it out on queries if needed, rather than using Date and then wishing you had Time.
 

Minty

AWF VIP
Local time
Today, 14:44
Joined
Jul 26, 2013
Messages
10,354
I'd agree with @Isaac - I use a variation on the Allen Browne function for this;
Code:
Function ServerDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by SQL Server.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Modified by Minty from a function by Allen Browne. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            ServerDate = Format$(varDate, "'yyyy-mm-dd'")
        Else
            ServerDate = Format$(varDate, "'yyyy-mm-dd hh:nn:ss'")
        End If
    End If
End Function

As for the the DataType either use DateTime or SmallDateDate if precision isn't required.
 

eatraas

Registered User.
Local time
Today, 07:44
Joined
Jan 23, 2009
Messages
96
There are 20 possible answers to this, but I believe the one most likely to survive regions, regional settings and database settings is:

'YYYY-MM-DD'

...That's what I always use.

If you're talking about what column datatype you need, then I would recommend DateTime, unless you have some extremely unusual time capture needs. DateTime will store the time element - but it's always better to capture that up front and strip it out on queries if needed, rather than using Date and then wishing you had Time.
There are 20 possible answers to this, but I believe the one most likely to survive regions, regional settings and database settings is:

'YYYY-MM-DD'

...That's what I always use.

If you're talking about what column datatype you need, then I would recommend DateTime, unless you have some extremely unusual time capture needs. DateTime will store the time element - but it's always better to capture that up front and strip it out on queries if needed, rather than using Date and then wishing you had Time.

Hi, of course the date is localized to our region, now it is formated as dd-mm-yyyy without the time. Thr firld format in Access is datetime format but the time option or data is not used.
 

Isaac

Lifelong Learner
Local time
Today, 07:44
Joined
Mar 14, 2017
Messages
8,738
You can use Date then in SQL Server if you want - is that your question?
 

Users who are viewing this thread

Top Bottom